DNA sequence data - requirement for a long and wide table of experimental data I need to build a database to hold data obtained from DNA sequencing experiments.

1) 5 million columns of data in one sample, and hundreds of samples in total

2) A few data points are null

3) Calculate the median, standard deviation etc. of each column every week

What would be an optimal design for this data and help to shorten the time consumed in analysis?

Any suggestion(s) would be appreciated.

Data type: DNA sequencing data. Break the whole chromosome into 1kb windows and calculate the mean depths of sequencing etc.

Columns: bed1, bed2...bed10000...

Rows: sample1, sample2...

Even if the focus is only on genes, there would be several thousand columns.

I wonder if there is any tool or solution to hand.

Is what you suggest a standard method of storing DNA sequencing results? Apart from the technical difficulties - good luck finding a database that will allow that many columns (SciDB?) - it will make querying tricky. I would look into ways of having your tables be tall and skinny rather than short and fat.

I would do something like the following:

CREATE TABLE sample
(
  bed INTEGER NOT NULL,
  sample_no INTEGER NOT NULL,
  ..
  .. other fields?
  .. chromosomal location? sequence? phenotype?
  .. dna_type (gene | promoter | mRNA | tRNA... &c.)?
  ..
  CONSTRAINT sample_pk PRIMARY KEY (bed, sample_no)
);

I believe that such a schema will facilitate querying and be far more technically feasible - it will conform far better to the relational model and will ultimately save you a lot of grief! I asked above Is this a standard method of storing DNA sequencing results? - this work has been done before so why are you reinventing the wheel?

Don't get me wrong - I'm all in favour of experimenting and trying to get a new perspective on biological phenomena, that's what science/molecular biology is all about, but others must have done this before - you may want to look at their work? For the record, I have a Masters in Cellular and Molecular Genetics from the Univeristé de Paris. HTH...


varchar(255) or varchar(256)? Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata.

Does it matter anymore at this point?

I saw some posts on the internet, however they apply to Oracle and MySQL.

We have Microsoft SQL Server 2016 Enterprise Edition, how does it apply to this environment?

Now say for example, what if I told my clients to keep for example, a text description to 255 characters instead of 256, is there any difference? What I read " With a maximum length of 255 characters, the DBMS can choose to use a single byte to indicate the length of the data in the field. If the limit were 256 or greater, two bytes would be needed." Is this true?

Size each and every column appropriately.  Do NOT use a "standard" size for each column.  If you only need 30 characters, why create a column that can handle 255?  I'm so glad you're not advocating using varchar(max) for your string columns.

This is especially prudent advice if you ever need to index a column, or if you are using a column as a primary key and it has foreign key references.  SQL Server uses the size of each column in it's query optimizer to understand estimated memory requirements for query processing.  Having oversized columns can be detrimental to performance.

Indexes on columns that are oversize can result in errors being generated:

CREATE TABLE dbo.WideIndex
(
    col1 varchar(255) NOT NULL
    , col2 varchar(255) NOT NULL
    , col3 varchar(600) NOT NULL
);

CREATE INDEX IX_WideIndex_01
ON dbo.WideIndex (col1, col2, col3);

The attempt to create the index above results in this warning:

  Warning! The maximum key length is 900 bytes. The index 'IX_WideIndex_01' has maximum length of 1110 bytes. For some combination of large values, the insert/update operation will fail.

900 bytes is the maximum key size for clustered indexes (and non-clustered indexes on SQL Server 2012 and older).  1700 bytes is the maximum key size for non-clustered indexes on newer versions of SQL Server.  If you design columns with a generic width, such as (255), you may run into this warning far more often than expected.

In case you are interested in storage internals, you can use the following tiny test to better understand how SQL Server stores uncompressed row-store data.

First, we'll create a table where we can store columns of various sizes:

IF OBJECT_ID(N'dbo.varchartest', N'U') IS NOT NULL
DROP TABLE dbo.varchartest;
GO

CREATE TABLE dbo.varchartest
(
    varchar30 varchar(30) NOT NULL
    , varchar255 varchar(255) NOT NULL
    , varchar256 varchar(256) NOT NULL
);

Now we'll insert a single row:

INSERT INTO dbo.varchartest (varchar30, varchar255, varchar256)
VALUES (REPLICATE('1', 30), REPLICATE('2', 255), REPLICATE('3', 256));

This query uses the undocumented, and unsupported, functions sys.fn_RowDumpCracker and sys.fn_PhyslocCracker to show some interesting details about the table:

SELECT rdc.*
    , plc.*
FROM dbo.varchartest vct
CROSS APPLY  sys.fn_RowDumpCracker(%%rowdump%%) rdc
CROSS APPLY sys.fn_physlocCracker(%%physloc%%) plc

Output will look similar to this:

╔═════════════════════╦════════════╦═════════╦══════════╦══════════════════════════╦══════════╦═════════════╦═════════════╦═════════╦═════════╦═════════╗
║ partition_id        ║ colName    ║ IsInrow ║ IsSparse ║ IsRecordPrefixCompressed ║ IsSymbol ║ PrefixBytes ║ InRowLength ║ file_id ║ page_id ║ slot_id ║
╠═════════════════════╬════════════╬═════════╬══════════╬══════════════════════════╬══════════╬═════════════╬═════════════╬═════════╬═════════╬═════════╣
║ 1729382263096344576 ║ varchar30  ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 30          ║ 1       ║ 1912    ║ 0       ║
║ 1729382263096344576 ║ varchar255 ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 255         ║ 1       ║ 1912    ║ 0       ║
║ 1729382263096344576 ║ varchar256 ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 256         ║ 1       ║ 1912    ║ 0       ║
╚═════════════════════╩════════════╩═════════╩══════════╩══════════════════════════╩══════════╩═════════════╩═════════════╩═════════╩═════════╩═════════╝

As you can see, the InRowLength for each value is shown, along with the physical storage location of each row - the "file_id", "page_id", and "slot_id".

If we take the file_id and page_id values from the query results above and run DBCC PAGE with them, we can see the actual physical page contents:

DBCC TRACEON (3604); --send display to the client
DBCC PAGE (tempdb, 1, 1912, 3); --database, file_id, page_id, 3 to show page contents
DBCC TRACEOFF (3604);--reset display back to the error log

The results from my machine are:

PAGE: (1:1912)

BUFFER:

BUF @0x00000000FF5B2E80

bpage = 0x0000000024130000          bhash = 0x0000000000000000          bpageno = (1:1912)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 32497                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000024130000

m_pageId = (1:1912)                 m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 98834 m_indexId (AllocUnitId.idInd) = 7936
Metadata: AllocUnitId = 2233785421652951040
Metadata: PartitionId = 1945555045333008384                              Metadata: IndexId = 0
Metadata: ObjectId = 34099162       m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 1                       m_freeCnt = 7538
m_freeData = 652                    m_reservedCnt = 0                   m_lsn = (35:210971:362)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x41 ALLOCATED  50_PCT_FULL
DIFF (1:6) = NOT CHANGED            ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 556

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 556
Memory Dump @0x000000005145A060

0000000000000000:   30000400 03000003 002d002c 012c0231 31313131  0........-.,.,.11111
0000000000000014:   31313131 31313131 31313131 31313131 31313131  11111111111111111111
0000000000000028:   31313131 31323232 32323232 32323232 32323232  11111222222222222222
000000000000003C:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000050:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000064:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000078:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
000000000000008C:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000A0:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000B4:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000C8:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000DC:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000F0:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000104:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000118:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
000000000000012C:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000140:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000154:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000168:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
000000000000017C:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000190:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001A4:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001B8:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001CC:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001E0:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001F4:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000208:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
000000000000021C:   33333333 33333333 33333333 33333333           3333333333333333

Slot 0 Column 1 Offset 0xf Length 30 Length (physical) 30

varchar30 = 111111111111111111111111111111

Slot 0 Column 2 Offset 0x2d Length 255 Length (physical) 255

varchar255 = 2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222

Slot 0 Column 3 Offset 0x12c Length 256 Length (physical) 256

varchar256 = 3333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3333333333333333333333333333333333333333333


SQL Server Profiler - Disable auto scroll by default Is there a way for new traces to start with auto scrolling disabled?

This is the very first thing I do every time I start a trace and I'd like to optimize my workflow, especially since I run multiple traces on numerous servers, and each one starts with this option enabled (even within the same instance of Profiler).

I would be open to an AutoHotKey workaround. For example, I use a script that detects when a window with ID 32770 is opened to auto-type "y" (to change a file extension in Windows Explorer it falsely matches the Outlook Rule name popup window though). Also note that it can take exorbitantly long for Profiler to open, because my machine is underpowered.

P.S. To add insult to injury, if you're too fast going to the menu to disable auto scroll on your new trace, it doesn't let you...

Here you go my good man,

Create the following MyProfiler.vbs file in insert the following code:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "PROFILER.EXE /S <your SQL Server Name> /T Standard"
WshShell.AppActivate "PROFILER.EXE"
WScript.Sleep(5000)
WshShell.sendkeys "%(WO)", True

Now, run MyProfiler.vbs.

Note:

It takes a few seconds to load Profiler.exe so sleep is set to 5 seconds. Otherwise, the auto-scroll commands will be input into the command window where MyProfiler is running. Cheers...

p.s.

You can run whichever template you prefer. I'm running the SQL Server Standard Template with /T.


INSERT trigger not SELECTing rows I'm working with a application and I have no control over the application source code, only SQL.

The entire process is this:

Row inserted via an application
Vendor trigger fires and calls an SP to insert the new row into my table. This new row is returned when I do a SELECT
I have an INSERT trigger on my table that selects the row and executes sp_send_dbmail to email the row just inserted (and will then truncate my table when live)

The issue is that I get the email (so the INSERT is picked up and the trigger executes) but it's blank.

If I then INSERT a second row, the trigger fires and I get an email containing the first row inserted.

My trigger:

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT
-- AFTER INSERT
AS

DECLARE @thequery NVARCHAR(MAX)

    SELECT @thequery =
    'SET NOCOUNT ON
     SELECT  contract,
         cont_line,
         site_ref,
         comp_id,
         cont_line as cont_line2,
         item,
         qty,
         ser_num,
         cust_num,
         description,
         unit_stat_code
    FROM tbl_UNIT_INS'

EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com,
     @query = @thequery,
     @subject = 'New Item Inserted'

I've tried using AFTER INSERT as well - same result.

It seems the trigger is firing before the row is inserted but the fact I get an email means the insert has happened so the insert has completed?

SQL Server 2012.

I think you will have better luck pulling from inserted instead of from the base table (and of course fixing the typo that should have prevented compilation), but you should also consider not using e-mail from a trigger at all.

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT -- AFTER INSERT is the exact same thing as FOR INSERT.
AS
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com', -- fixed missing quote here
     @query = N'SELECT * FROM inserted;',
     @subject = 'New Item Inserted';
END
GO


Which view contains all Oracle's catalog views? I don't remember and cannot find the name of the view containing the list of all Oracle's catalog DBA_%, ALL_% and USER_% views.
Any help?

DICTIONARY

DICTIONARY contains descriptions of data dictionary tables and views.


Database logon trigger To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors.

Create or replace trigger you_may_not_login
after logon on database
begin
if sys_context('USERENV','SESSION_USER')='xx' AND     sys_context('USERENV','IP_ADDRESS')='10.0.30.219' then
raise_application_error(-20001,'Denied!  You are not allowed to logon the database');
end if;
end;
/

When I login using the 'xx' user, the alert log shows the following error but access IS granted to the database, the trigger somehow fails from blocking the login of the user.

Errors in file /oraarch/core/udump/wfsbi_ora_9338.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Sorry, you are not allowed here!
ORA-06512: at line 9

Where am I going wrong?/

You might want to verify this in SQLPlus.  If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes:

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx';

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219';

SELECT 'Check This' FROM user_role_privs WHERE granted_role='DBA';

SELECT 'Check This' FROM user_objects WHERE Object_Name='YOU_MAY_NOT_LOGIN' AND Object_Type='TRIGGER';

SELECT 'Check This' FROM User_sys_Privs WHERE Privilege='ADMINISTER DATABASE TRIGGER';

If any of these return Check This then one of your assumptions is incorrect.

For me all that was necessary to be disconnected was the RAISE_APPLICATION_ERROR, but another site uses an EXECUTE IMMEDIATE 'DISCONNECT'; as well.  Perhaps this allows it to work even when the DBA role has been granted.  I have not tested it to know.

You should also be aware that this should not be used for security in place of secure passwords and other measures as the IP_ADDRESS the client sends can be altered.


How much RAM should I get for a cloud-hosted PostgreSQL data warehouse? I'm looking at migrating a current PostgreSQL data warehouse to a cloud host with SSD storage and RAM as one of the main sizing variables. The bulkiest data we're dealing with at the moment will live on monthly partitioned tables. Each month is about 70GB with indexes (40-ish w/o). Data is likely mostly bulk-loaded periodically, and will then be accessed by a small team of 5 researchers.

I've been trying to search for recommendations for spec'ing RAM on this site and all I've found is:

Fit the entire DB (>1TB, unrealistic)
More is better

Should there be enough RAM to at least load an entire index (16GB) to RAM? Are there any other details I should provide?

Estimating database server memory size by the size of tables (or indexes) is going to be misleading. On the one hand, depending on the database workload, it may not ever need to load the entire table (or index) in memory. On the other hand, there are many other possible memory consumers that have little or no correlation to the data size, such as sort buffers or client connections.

If your current server is not memory-bound, then you can use its configuration as a guideline. Otherwise you might as well buy yourself as much memory as you can afford.


Mixing Clustered Instance and Non-Clustered Instance I'm currently managing a 2 node SQL Server Failover Cluster in Active/Passive setup. I plan on adding a new SQL Server instance which doesn't have to be on the Failover Cluster. The only documentation I found is about installing additional Failover Cluster Instances. I wonder if there's any set back when mixing them

EDIT: The new instance is for application testing purposes. It's not related to those in the cluster.

How is the new instance intended to be used? Do the databases destined to be on it relate to those in the cluster at all? (please add this detail to the question so others will see it easily)

If it is not part of the cluster then isn't it just a stand-alone instance that needs no unusual care? You should just be able to install it as normal.

If it is being installed on the same machine as one of the clustered instances then be careful about memory use: instances are not terribly good at sharing memory between them so if the new instances is going on the passive of the pair it may claim much RAM and then if/when a fail-over occurs the clustered instance will find very little available when it needs to take over from the previously active one. There are settings to limit the amount of memory an instance can use, look into setting those if you are adding a new instance to one of the existing machines partaking in the cluster.


PostgreSQL remote access I added remote host info on pg_hba.conf as:

host db1 all 10.6.169.88/27 trust

and also I did

listen_addresses = ‘*’
tcpip_socket = true

on postgresql.conf.

But still the remote client can not connect to db. What is the missing part about this? Remote client and host server is in different locations but with IT policies adjusted with correct ports and routes. So remote client is normally accessible to host server.

Here is what remote client gets as error:

It worked after editing the correct pg-hba.conf file. Well, earlier our customer asked us to move postgresql data to other drive partition. So there are two copies of conf files. One is inside postgresql program folder and the other is in data folder. To achieve what I want I needed to edit the one inside data folder.


Database size and cycle I am developing a desktop application and it will include clients, bills, banks, incomes&outgoings etc,

I am using mysql and delphi,

My question is do i have to cycle (create new tables and transfer needed data from old tables to new tables for new year) every year because i think database will have data for a year and it will adventually get slow, is there a way/is it possible to continue without cycle but without database get slow?

I want to do this because i want user be able to find/see client cards and bills of old years too

Thanks

With rare exceptions, do not create identical tables split by year or month or customer or whatever.  If a table is getting slower as it gets bigger, then you don't have the right indexes and/or you need to redesign the queries.  Even a billion rows in a table is not 'unmanageable'.

Clients, bills, banks, transactions -- sounds like about 4 tables, plus some tables for Normalization.  A bill might contain a client_id column, but not the client's name, address, etc.


inaccurate statistics in multitenant database We are using Azure SQL and have "multi tenant" database. All tables have tenantid + entityid pk. Some tenants have a lot of data and some tenants quite small.
Also auto update statistics is enabled.

Sometimes (quite often recently) after auto update of statistics  we get inaccurate statistics (depending on sample) and according to it tenant has 1 row (but actually 10-100k).
This leads to bad execution plans and performance problem for the whole db.

Maybe somebody has ideas how to fix it.

My suggestion with Azure SQL Database, perform index maintenance during off-hours using Ola Hallengren's scripts and after that update all columns statistics with full scan. Do not rely on automatic features on medium size databases (> 100 GB).

Here you will find steps to create elastic jobs to update indexes and statistics using Ola Hallengren's scripts.

Another way to perform maintenance of indexes and statistics can be found here using Azure Automation and PowerShell.


MySQL - run SELECT statement on another server without defining the table structure In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.

In MS SQL Server, however, I can run any SQL statement against a linked server. Is it possible to do the same thing in MySQL?

How is a "linked server" connection different than any other connection defined by a MySQL client?

  In MS SQL Server, however, I can run any SQL statement against a
  linked server. Is it possible to do the same thing in MySQL?

Maybe I'm missing something but yes, just setup a connection.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1

import MySQLdb
db = MySQLdb.connect(host="localhost", user="<user>", passwd="<password>", db="<database>")

You could go so far as setting up a reverse tunnel with SSH.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1 -P 33061

Other than that, FEDERATED tables are the closet thing.

I must be missing something.


How to repair database consistency errors I have a Microsoft Dynamics AX SQL Server database. I just run DBCC CHECKDB in order to check the integrity. After a few minutes I got at the end of the results the following:

  CHECKDB found 0 allocation errors and 4 consistency errors in database
  'AXPROD'. repair_rebuild is the minimum repair level for the errors
  found by DBCC CHECKDB (AXPROD). DBCC execution completed. If DBCC
  printed error messages, contact your system administrator.

Since I got 4 consistency errors I would like to know if there is a way to fix this, or a way to get more detailed information about these errors.

I would like to know if there is a way to fix this

These consistency errors may be fixable with the REPAIR_REBUILD option of DBCC CHECKDB:

  Performs repairs that have no possibility of data loss. This can
  include quick repairs, such as repairing missing rows in non-clustered
  indexes, and more time-consuming repairs, such as rebuilding an index.

As Shanky's answer mentions, any DBCC repair should also be performed inside a transaction, so you can inspect the changes before committing to them.

As always, please ensure you have a completely recoverable set of backups (including the log tail if applicable) before running the rebuild. If you have a complete set of valid backups (including the log tail as applicable) and you can afford the downtime, restoring might be the preferred option. Be sure not to overwrite the current database if you do this, just in case the restore fails, or it is not as complete as you expected. Of course, it's quite likely the restored database would contain the corruption again, depending on how and when it occurred :)

  or a way to get a more detailed information about this errors

Details of the four consistency errors are in the DBCC CHECKDB output, before the summary section at the end. You should review these to ensure you understand the problem, and what may have caused it, before attempting any repair.

You can reduce the amount of DBCC CHECKDB output using the WITH NO_INFOMSGS option.

Add the DBCC error message details to your question if you need help analyzing the errors. It is important to identify and correct any underlying hardware problem that might have caused the corruption.

Depending on the details of the corruption, there may be other ways to fix the problems (such as manually rebuilding a nonclustered index).

If the repair or rebuild is successful, you will need to check the database again with DBCC CHECKDB with the fullest set of checks supported by your version of SQL Server.


How can we manage cross-database dependencies across environments? I've put off asking this question for some time since it's tricky to
encapsulate our situation and challenges without a wall of text, but the
situation is getting worse so I'll do my best. I'm looking for some assistance in improving the way we develop and manage our application database and developer environments, particularly in regards to database dependencies across environments.

About us

We're a medium-sized company with a fair amount of legacy code. To give an sense of what our current application database looks like, here are some ballpark numbers: 50gb, 450 tables, 200 views and 400 stored procedures. In addition, our production server runs approximately 15 databases, most of which needs or is needed by our application database.

To clarify: When I say "need", I'm referring to database objects that will not
compile/will compile but not run without the dependency. Examples of these
objects are server objects like Linked Servers and Replication Subscriptions, or
database objects like Stored Procedures and Views.

Over the past year, we've made significant improvements to the way we develop
and deploy our database. Improvements so far include the introduction of
dedicated developer environments, version control of (nearly) all database code,
automated deployment from Git (trigger-based) and the transition to a SQL Server
cluster.

The issue

What we're struggling with, and which I cannot seem to find suitable resources for, is how to deal with dependencies from our application database to other databases. These dependencies fall into two separate challenges:

1. Databases on the same server

At the moment of speaking, our application database depends upon 5 databases on the same server. These are databases with separate repositories, deployment pipelines, libraries and web projects. While bootstrapping developer environments, we must take care to create these in a particular order in order to successfully apply DDL and DML-scripts, lest we face dependency errors. This process alone is causing a lot of headache. In fact, it is causing so much headache that some of our developers have simply given up on local developer environments and do all of their development in a shared database.

2. Databases on a remote server only accessible to production

In our production environment, we import data from a handful of remote SQL Server instances. Some of this data is imported using stored procedures that reference remote servers using Linked Server objects. In order for the stored procedure to run, the Linked Server object needs to exist. In order for the Linked Server object to "successfully" exist, the remote server it references must be reachable. The remote servers are only accessible from our production server (and rightly so), but this causes our stored procedures not compile properly during deployment.

In the book "Continuous Delivery", author Dave Farley emphasizes that in true Continuous Integration, every single resource required to assemble and run a project should reside in its repository. Additionally, he specifies that every environment should be identical (with the exception of configurations such as credentials and connection strings). Our application does not satisfy these principles, and I'm not even sure if it's feasible to do so.

Our tools

Database server: Microsoft SQL Server 2017
Build server: Visual Team Services
Build tools: Redgate DLM Automation Suite

It feels like I'm missing out on some core architectural principles here. What can we do to alleviate these issues? Suggestions to relevant literature are also welcome.

2. Databases on a remote server only accessible to production

  ... every environment should be identical (with the exception of configurations such as credentials and connection strings)...

With regards to Linked Servers, this one is actually not so difficult. You just need to classify them correctly: they themselves are a component of the system and should be identical across environments, BUT their definition is a configuration that is often different between environments. Meaning, what needs to be consistent across environments is the fact that the Linked Server exists, and its name. This way any and all references to the Linked Server(s) can be identical across environments.

For non-Production environments, you just need to set up an Instance to be a dummy target of the Linked Server so that validations made against it when code is compiled will succeed. This can be achieved with SQL Server Express if need be. You don't need any data in this Instance, just the Schema that is explicitly referenced in your code. You can share one of these dummy targets between environments, but you should probably have two of them: one for the current structure, and one for changes being made in those remote resources that you will need to test against before they go live (assuming that you are given notice of upcoming changes).

And in case it is not obvious, these dummy targets are shared resources, regardless of whether devs are developing against private, local Instances on their workstations or against a shared Instance.

The Schema for the dummy targets is not mixed in with your app / DB code. It is either considered configuration or as a component of your build process / CI process.

  1. Databases on the same server

  (quoted out of order) While bootstrapping developer environments, we must take care to create these in a particular order in order to successfully apply DDL and DML-scripts, lest we face dependency errors... it is causing so much headache that some of our developers have simply given up on local developer environments and do all of their development in a shared database.

I understand the idealism behind developing against private DBs to prevent external interference, but in practice I am not sure how necessary it is. I worked in an environment that had easily 20 DBs for 7 - 10 products, over 1000 tables, over 3000 stored procedures, etc. We had easily 7 product teams. Some of the DBs were dedicated (i.e. isolated) to a particular product, and some where shared across multiple products. And there were other complexities that I am leaving out, but the point is that it was a more complex environment than what you are describing. We had shared DBs in all environments: dev, QA, staging, etc. In the 6 years I worked there I can think of maybe a handful of times where there was cross-team interference. And when there was, we just discussed the situation with each other and came up with a way to not impede each other. Sometimes we rearranged the order in which we worked on particular stories / tasks so get the dependency done sooner, or if that was not possible, we might stub something out / make a partial change for something that would be done soon but didn't have time to get to right then. I don't remember there being a time when a team couldn't proceed at all (and if I am forgetting, then it was still quite rare). If the dependency was that large of a factor for the story / task, then we usually discussed it before planning the sprint so that we would know if our story could even proceed, or if it had to be punted to the next sprint while the other team made their changes.

All of that to say, getting this to work on dev workstations for private development sandboxes is a bit of work and it might not be worth the investment of time in getting this working. Especially because it ain't just boot-strapping that you need to handle. If it was merely an issue of initial setup, then I would still recommend restoring the most recent backup of the shared dev Instance (because you will still have one of those anyway, right, for when code is checked in? just like there should be a shared app server that gets deployments from all teams to make sure that everything compiles correctly before migrating up to QA). I don't see any benefit in running SQL scripts as that still leaves the private Instance with no data. And how much time do you want each dev spending on adding rows to tables so that they can test their change? Not only is that a waste of time, but it is highly error prone as we all know that they won't go through the app (and hence the app logic), but will simply do direct inserts or use SSMS to edit the table(s) to add rows. And they will add the bare minimum number of rows with only ideal conditions. So you will end up getting more instances of code that doesn't functionally work against real data, along with the dev saying, "well, it works on my box".

But it's not just a matter of initial population of structure (and hopefully data). You still need to push down committed changes from others. And that might even be more likely to fail now since another team might have completed their project that either breaks when being pushed down to the dev workstation, or changes from a dev workstation can't merge into the shared DB because the environment changed. In a shared Instance, these interdependencies are found and accounted for much earlier in dev cycle.

All of that to say, if your developers are not currently blocking each other semi-frequently, then I recommend sticking with your current setup of a shared Instance.

  ... These are databases with separate repositories, deployment pipelines, ...

Separate repositories, libraries, etc is not a problem. But separate deployments could certainly complicate things, the degree to which depends on what direction(s) the dependencies are in. Do dependencies ever go both ways between projects, or always from one project/DB to another?

I do not put much faith in fully-automated database deployments. Checking in code and having a process blindly take it all and do something with it works fine for app code since app code is just that: code. You take the committed / checked-in files, compile them, and you have your finished product (typically). If there are dependencies between libraries then they are handled by building the projects in the correct order.

But databases, by their very nature, are more than just stateless code: they also contain state. And so I don't see how one gets around custom deployment scripts (outside of singular databases and/or not much data). I know some folks are able to use automated migration scripts, but I have never worked in an environment that could. So as much as I strongly advocate for keeping all code in source control, I would never consider generating deployment scripts from it.

Due to our complex structure (and the number of servers / DBs we were deploying to — same schema deployed to anywhere from 4 up to 20+ instances, depending on the product, and multiple DBs per each Instance — we had a custom deployment system. It was multi-threaded and quite nice, actually (mostly using nAnt and CruiseControl, I believe). Our process was to have a separate release script folder (per each release), in the repository (separate from Trunk) that the build system processed, triggered by checkins. We had folders for each project, and within each project folder, we had folders for Schema (Tables, Views, and Triggers) and Data, Functions, Stored Procedures, etc. When we updated stored procedures and functions (i.e. code) we checked the object script into both our team branch and to the appropriate release folder. All release scripts were required to be idempotent (i.e. re-runnable). The build process processed the folders in the same order each time, and ran the Schema and Data folder before the "code" folders. For Schema and Data changes, we checked the object script (basic CREATE statement) into the team branch, but a custom migration script into the release script folder. This allowed us to properly handle transactions, dependencies, etc. Also, per each release script folder, all scripts were prefixed with a 3-digit order number: 001 - date - this change.sql, 002 - date - shortDescription.sql, etc. This way the release was processed in the same way each time it ran, and it ran a couple of hundred times over the course of the 3-week sprint. If we were working in a DB that was a shared resource and we had a lot of changes to make, we would simply "reserve" the number of slots we needed by checking in empty .sql scripts with the first and last order numbers we needed. Everyone always did an update before committing anything, so we always knew what the next available order number was. Worst case scenario, we renamed a script to adjust the order number if two people checked in the same number at roughly the same time (but again, this was quite infrequent, even with 10 of us constantly checking stuff in).

One tricky thing was managing the Linked Servers. We had one DB were all Linked Servers existed (the rule was that they would only ever be in that one DB). And, to make it even better, we were using Replication and had to ensure that schema changes were made at the subscriber before data changes were made. To make this work, we had two release script folders for this one DB: one of them was the first release script folder to process, the other was the last release script folder to process. Schema changes went into the "first" folder so that the changes were in place for other DBs with dependencies to it, and data changes went in the "last" folder since by this time, the publisher and subscribers had their schemas updated.

Hopefully this gives you some direction and ideas for handling these complexities of dependencies and Linked Servers, etc.

Good luck.


Drop datafile without dropping a tablespace in Oracle 11g I was trying to take backup of database in Oracle 11g by using RMAN

      $ rman target /
    Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 11 10:14:37 2015

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: ORCL (DBID=1415405400)

RMAN> backup incremental level 0 as compressed backupset database;

Starting backup at 11-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/11/2015 10:16:58
RMAN-06056: could not access datafile 5

I have misplaced the datafile then I have added that datafile executing a touch command but still I was getting an error that the database is unable to lock the file. I put the database in mount mode then I took that file offline and after I started backup  there was the same error. So how can I solve this error without getting tablespace deleted

The Database Backup and Recovery Reference says that you can do a backup by

backup incremental level 0 as compressed backupset database skip inaccessible;

or better

backup incremental level 0 as compressed backupset database skip offline;

But your problem is that the data from datafile 5 is lot if do do not have a valid backup.

If you have a valid backup vou can restore datafile 5.

If you don't have a valid backup you have two option:

you drop the tablespace and everything from this tablespace will be removed forever
export as much as possible , drop and recreate the tablespace and import what you have exported before.

"export as much as possible" means:

you can export the definitions of the objects from this tablespace because these definitions reside in the SYSTEM tablespace.
if the tablespace consists of more than one datafile you can export the data from all remaining datafiles of this taplespace.

These export can be done with the expdp datapump tool.
Use the TABLESPACES parameter to specify the name of the tablespace you want to export. If you only want to export the definitions of the objects set CONTENT=METADATA_ONLY

The import can be done with the impdp tool.


What to call a column that references another row in the same table? I have inherited a table that contains a column that is used to "chain" between various related rows within the same table.

For instance:
id, bar, reference_id

1, 'foo', 2
2, 'foo', 3
3, 'foo'

I have no idea what to call this structure in order to search for how I should deal with it.   The idea is that the table maintains a sort of living history within itself.   (my problem is that I may have row 1 or 2, but I need to get to row 3 somehow that is agnostic to the number of 'levels' that I need to traverse)

There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.

What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.

They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!

Something like:

SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;

... will get you started on your data.


Recursive Parent-Child Location Relationship Cache as View Here is my data structure:

CREATE TABLE Locations(
    LocID int IDENTITY(1,1) NOT NULL,
    LocationTypeID int NOT NULL,
    ParentID int NULL,
    LocNum varchar(50) NULL,
    LocName varchar(250) NULL,
)

CREATE TABLE LocationTypes(
    LocationTypeID int IDENTITY(1,1) NOT NULL,
    TypeName varchar(50) NOT NULL
)

The LocationTypes table contains the following values:

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+

The Locations table contains a bunch of locations, some where the ParentID is null (Campuses), and others that have a ParentID. There are 5 levels of hierarchy to my data structure, indicated in the LocationTypes table. Campus (1) being the least specific, and Room(5) being the most specific. In my database I have other tables which reference the LocID, most often these tables are referencing Locations with a LocationTypeID of 5, but not always.

I have a need to get the names of every Location in the hierarchy for display to the end-user.

Ultimately what I want to get is a view (or cached table) with the following structure:

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+

I realize that some of these columns will end up being null, for Locations with a LocationTypeID of anything other than 5, and that is perfectly fine.

I know that I probably need a recursive common table expression, I just don't really know where to start.

Basically the pseudo-code logic for this is:

Foreach Location in the LocationsTable
Get the Parent Location of each Location, continue recursively until there are no more parents
Store this cached data in a view, so that it can be easily LEFT JOINed on any other table that references a LocID

I hope that all makes sense. Thanks anyone that can help me.

I believe you can achieve the desired result with this query:

;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
    SELECT LocID,
        LocName AS CampusLocName,
        CONVERT(varchar(250), NULL) AS BuildingLocName,
        CONVERT(varchar(250), NULL) AS AreaLocName,
        CONVERT(varchar(250), NULL) AS FloorLocName,
        CONVERT(varchar(250), NULL) AS RoomLocName
    FROM Locations
    WHERE ParentID IS NULL

    UNION ALL

    SELECT L.LocID,
        CampusLocName,
        IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName,
        IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName,
        IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName,
        IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
    FROM Locations L
        INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;

According to the WITH common_table_expression doc:

  This clause can also be used in a CREATE VIEW statement as part of its
  defining SELECT statement.


InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup

I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,578559,579891#msg-579891

2013-12-06 21:08:00 7f87b1d26700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-12-06 21:08:00 7f87b1d26700 InnoDB: Recalculation of persistent statistics requested for table "drupal"."sessions" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-12-06 21:08:07 7f903c09c700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

Any official solutions or 100% fixes?

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats

These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually:

innodb_index_stats

USE mysql;
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

innodb_table_stats

USE mysql;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

slave_master_info

USE mysql;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

slave_relay_log_info

USE mysql;
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

slave_worker_info

USE mysql;
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

ALTERNATIVE

Another workaround would be to create the script from a MySQL 5.6 instance.

Step 01 : Goto a DB Server running MySQL 5.6, or install MySQL 5.6 on a test  machine.

Step 02 : mysqldump these 5 tables to a text file

INNODB_TABLES="innodb_index_stats"
INNODB_TABLES="${INNODB_TABLES} innodb_table_stats"
INNODB_TABLES="${INNODB_TABLES} slave_master_info"
INNODB_TABLES="${INNODB_TABLES} slave_relay_log_info"
INNODB_TABLES="${INNODB_TABLES} slave_worker_info"
mysqldump -uroot mysql ${INNODB_TABLES} > InnoDB_MySQL_Tables.sql

Then, you can run InnoDB_MySQL_Tables.sql on any DB Server running MySQL 5.5 before upgrading.


Monitoring DB activity in a large server environment Apologies if this is ambiguous. In an environment with a small number of servers it is relatively easy to install and maintain stored procedures, etc. I normally build up some stored procedures to notify by DB mail when there are events that I should be aware of,  maybe build some triggers to notify when there are new databases in the system.

How do I build, deploy and maintain these kind of objects in an environment with a large number of servers where it becomes impossible to do so individually.

Secondly, we use log forwarding on all individual servers except for the central management server, and only have DB mail active on the central management server. How can I return relevant results to the DBAs when I can't use DB Mail?

A couple months ago I wrote this script that can execute through SQLCMD your dynamic SQL on a comma-delimited-list of servers.

Note that you have to copy/paste the result into a new window in SSMS, then change it to SQLCMD mode, remove any non-SQL lines, and then execute.

I've used this a few times and it works. It's not a shrink-wrapped, commercial deployment tool, but it will work.

My first use case that inspired this script is that I inherited a bunch of servers from another DBA, but their email address was still in some of the Agent Operators. Instead of searching through every server for that person's email address and updating it to my own, I wrote this script to automate the process. The dynamic SQL can be adapted to any purpose.

Be sure to test this very carefully.

/*
    This script will create a TSQL script that you can run separately in SQLCMD mode.

    Steps:
    1. Skip this:  Create the function SplitString with the commented-out script below.
    2. Edit @Servers with a comma delimted list of your servers.
    3. Edit @SearchVariable1 and @SearchVariable2 with the search variables.
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me.
        You can add more SearchVariableN variables. Edit the dynamic SQL statement.
4.  Edit the dynamic SQL to do whatever you need.
5. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
6. If logical, you can re-run the script to check the results.

*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)=','
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''
DECLARE @SearchVariable1 VARCHAR(MAX) = ''
DECLARE @SearchVariable2 VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,server2,server3,server4'

SET @SearchVariable1 = 'person1'
SET @SearchVariable2 = 'person2'
-- ************

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10)

DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @ThisServer

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'SELECT ''' + @ThisServer + ''' AS [Server] ' + @B
    SET @SQL = @SQL + 'SELECT * FROM msdb.dbo.sysoperators ' + @B +
        'WHERE email_address LIKE ''%' + @SearchVariable1 + '%''' + @B +
        'OR email_address LIKE ''%' + @SearchVariable2 + '%'''
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    -- Use this to update the operator. This only works when there is only one email address for the operator.
    -- ToDo: adapt this to replace the email address within a list.
    --SET @SQL = @SQL +
    --  'USE [msdb] ' + @B +
    --  'GO ' + @B +
    --  'EXEC msdb.dbo.sp_update_operator @name=N''DFK_Team'', @enabled=1, @email_address=N''me@myemail.com''' + @B  +
    --  'GO '
    --SET @SQL = @SQL + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString


Backup a sample of a PostgreSQL database Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)?

I am able to back up that database using pg_dump. In some case I need db with minimum data.

pg_dump --host=localhost --port=5432 --username=postgres --password
        --column-inserts --schema=test  testdb > test_backup.sql

How can I modify the above command to take the back up with 1000 number of  data??

I had a similar problem, I wanted to copy the most recent rows from a few tables from one db to another hosted on a different server, and I ended up writing a bash script that executes the pg_dump command followed by various psql commands:

#!/bin/bash
declare -a arr=("my_table_1" "my_table_2" "my_table_3")
startdate=2014-12-01
enddate=2014-12-30
for table in  "${arr[@]}"
do
    echo -e \\n$table\\n$(for each in $(seq 1 ${#table}); do printf "-"; done)
    pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c | psql -h host_name_2 -U my_username db_name_2
    psql -h host_name_1 -U my_username db_name_1 -c "\copy (select * from $table where date >='$startdate' and date <='$enddate' order by date desc) to '/tmp/data.csv' csv header;"
    psql -h host_name_2 -U my_username db_name_2 -c "\copy $table from '/tmp/data.csv' csv header"
done

Line by line in the body of the loop:

the echo is just prettyfication: it prints the name of the table and underlines it like a title,
the pg_dump command generates some SQL to copy the table schema from the source and performs a few more SQL housekeeping commands (e.g. the -c flag means that the SQL will contain a command to delete the table first before creating it), and this SQL is piped with | to psql pointing at the destination (you can try running just the pg_dump part at the comand line, pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c, and see the SQL it generates),
the psql on the next line copies a table in the source db into a csv file,
the final psql copies from that csv file into a table in the destination db.


Oracle 11g Enterprise Manager Login Fail Internal Error has Occured I have installed Oracle 11g on my Windows 10 machine. I followed all preinstallation requirements.

When trying to log in to the Enterprise manager via this link:

https://localhost:1158/em

I get the error:

  Internal Error has occurred. Check the log file for details.

I have found log files and it looks like this is the relevant entry:

2017-09-17 07:36:53,631 [MetricCollector:HOMETAB_THREAD600:60] ERROR rt.DbMetricCollectorTarget _getAllData.344 - java.lang.NullPointerException
java.lang.NullPointerException
    at oracle.sysman.emSDK.emd.comm.EMDClient.getURL(EMDClient.java:1570)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getConnection(EMDClient.java:1266)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getConnection(EMDClient.java:1242)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getResponseForRequest(EMDClient.java:1689)
    at oracle.sysman.emSDK.emd.comm.EMDClient.getMetrics(EMDClient.java:981)
    at oracle.sysman.emo.perf.metric.rt.DbHomeTab._getAllData(DbHomeTab.java:340)
    at oracle.sysman.emo.perf.metric.rt.DbHomeTab.getData(DbHomeTab.java:154)
    at oracle.sysman.emo.perf.metric.eng.MetricCached.collectCachedData(MetricCached.java:416)
    at oracle.sysman.emo.perf.metric.eng.MetricCollectorThread._collectCachedData(MetricCollectorThread.java:605)
    at oracle.sysman.emo.perf.metric.eng.MetricCollectorThread.run(MetricCollectorThread.java:325)
    at java.lang.Thread.run(Thread.java:595)

I have tried to set the unique name in environment variables, I thought maybe Java was the problem so I updated my JDK to 8. I am still getting the same error, and my SQL Plus will not open, as soon as I put in the correct credentials it closes. Also I have configured a Listener and tested, test was successful.

Please help.

I managed to fix the issue, turns out it was a time zone issue. The DB time zone and computer time zone were different. Thus the DB Console would not start and was giving the error "EM daemon is not running".
I fixed it by following instructions here.

Also note before I did that I completely uninstalled Oracle and reinstalled it. There were some issues on that install that were causing SQL not to run. But the above is what fixed the Internal Error I was getting as I got it again even after the fresh install.


Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID Someone in their wisdom created a table called user:

select schema_name(schema_id)
,*
from sys.tables
where type = 'U'
 and name like '%user%'

 select top 100 *
 from usr.user

  Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword
  'user'.

I have many scripts where I pass the object name and I need to check where the name passed is valid (exists in the current database) or not.

some are failing when I pass the tablename usr.user as a parameter.

so I had a look as how sql server does this, using sp_help as an example

and I came out with something like this:

--=======================================================
-- check for the existence of object name or data type (system or user)
-- within the current database
-- works on sql server 2016 but probably lower versions too
-- marcelo miorelli
-- 18-july-2017
--=======================================================
SET NOCOUNT ON

declare @objname varchar(108) = NULL  -- object name we're after
declare @dbname varchar(108) = NULL  -- object name we're after

select @objname = 'ipv4' -- this is the parameter to be passed

-- Make sure the @objname is local to the current database.
 select @dbname = parsename(@objname,3)
 if @dbname is null
  select @dbname = db_name()
 else if @dbname <> db_name()
  begin
   raiserror(15250,-1,-1)
   --return(1)
  end

 -- @objname must be either sysobjects or systypes: first look in sysobjects

 declare @objid int
 declare @sysobj_type char(2)

 select @objid = object_id,
        @sysobj_type = type
   from sys.all_objects
  where object_id = object_id(@objname)

 -- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
 if @objid is null
 begin
  -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
  select @objid = type_id(@objname)

  -- IF NOT IN SYSTYPES, GIVE UP
  -- in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects)
  -- and it's recommended as a best practice to use the views instead,
  -- sys.columns, sys.objects, sys.types, etc

  if @objid is null
  begin
   raiserror(15009,-1,-1,@objname,@dbname)
   --return(1)
  end
end

select [@objname] = @objname
      ,[@objid] = @objid
      ,[@sysobj_type]=@sysobj_type
      ,[@dbname]=@dbname

example with usr.[user] as parameter

usr.user as parameter

and I even went off and created a user defined data type just to test
ipv4

question:
is there a better(performance wise)\more standard way to get this done?

Some notes:

You can get rid of PARSENAME and get the object name from your existing sys.objects query since you are using the OBJECT_ID() built-in function to handle getting the object regardless of how it is named.
Getting rid of PARSENAME means that you won't be handling Database names, but the comment at the top of your original code in the question does specify that you are only concerned about checking "within the current database".
You can then JOIN to sys.schemas to get the Schema name at the same time.
It is very easy to concatenate the SchemaName and ObjectName together, so leave them separate so that you can deal with the pieces if needed.
You do not need / want an IF block to check for Types since that ignores an edge case. Objects and Types are in two separate tables so there is no uniqueness enforced for names between them. Meaning, any schema-bound object residing in sys.objects can have the same name as a type in sys.types. You need to handle the case where an object and a type both have the same schema and name.
Schema names and Object / Type names should be wrapped in square brackets via the QUOTENAME built-in function.
There are four different types of Types: System, User-Defined Data Types (UDDT), SQLCLR User-Defined Types (UDT), and User-Defined Table Types (UDTT). You will probably need to distinguish between these as they aren't always handled the same way.
Always use NVARCHAR for names of anything in the system: object, schema, column, type, index, job, assembly, queue, database, login, server, etc, etc. Never use VARCHAR. Most names are defined as being the sysname type, which is just an alias (that lives in master but is available everywhere, similar to system stored procedures) for NVARCHAR(128).

When dealing with a single-part name, use sysname (there are a few instances where sysname isn't being used, but this should generally always work since most names never get that large anyway). When dealing with multi-part names, you need to increase the size to account for each part plus the square brackets plus the connecting period / dot. Here we are handling SchemaName + ObjectName, so that is 128 for each "name" + 4 square brackets + 1 dot between them = 261 max characters. If you needed to also account for Database names, that would be another 131 characters (128 + 2 + 1).

The reasons for using NVARCHAR instead of VARCHAR are:

At the most basic level, this should be done for datatype consistency in your code. Since names are all internally NVARCHAR, using VARCHAR causes explicit conversions everywhere you those parameters / variables / columns (if storing names in tables).
Since all of the names in the system are internally stored as NVARCHAR, it is technically ok to use any Unicode characters in any name, including combining characters, supplementary characters, etc. Yes, there is a note that supplementary characters don't work in Database names, but they actually ;-) (there are just some nuances to deal with in that case). So, if you use VARCHAR for parameters / variables, you could be a mangled version of the name and not the name itself, which will produce odd behavior.

The Inline Table-Valued Function (ITVF) below encapsulates all of the above suggestions. The system Types can be further broken down into base types (INT, VARCHAR, etc) and CLR types (HierarchyID, Geometry, and Geography) if need be, but here they are all just system "Types".

Inline-TVF

CREATE FUNCTION dbo.GetObjectInfo (@Name NVARCHAR(261))
RETURNS TABLE
--WITH SCHEMABINDING -- cannot schema bind due to referencing system objects
AS RETURN
SELECT obj.[object_id],
       QUOTENAME(sch.[name]) AS [SchemaName],
       QUOTENAME(obj.[name]) AS [ObjectName],
       obj.[type]
FROM   sys.all_objects obj WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
        ON sch.[schema_id] = obj.[schema_id]
WHERE  obj.[object_id] = OBJECT_ID(@Name)
UNION ALL
SELECT typ.[user_type_id],
       QUOTENAME(sch.[name]) AS [SchemaName],
       QUOTENAME(typ.[name]) AS [ObjectName],
       CASE typ.[is_user_defined]
         WHEN 1 THEN CASE
                       WHEN typ.[is_assembly_type] = 1 THEN 'UDT'
                       WHEN typ.[is_table_type] = 1 THEN 'UDTT'
                       ELSE 'UDDT'
                     END
         ELSE 'Type'
       END AS [type]
FROM   sys.types typ WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
        ON sch.[schema_id] = typ.[schema_id]
WHERE  typ.[user_type_id] = TYPE_ID(@Name);
GO

Tests

USE [tempdb];
CREATE TABLE dbo.[user] (Col1 INT);
CREATE TYPE dbo.[user] FROM INT NOT NULL;
CREATE TYPE dbo.[table] AS TABLE (Col1 INT);

SELECT * FROM dbo.GetObjectInfo(N'dbo.user'); -- returns 2 rows
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[user]'); -- returns 2 rows

SELECT * FROM dbo.GetObjectInfo(N'dbo.table'); -- returns 1 row
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[table]'); -- returns 1 row


Oracle 12c Datapump import error I am importing a database (.dmp) file using the 12c Data Pump import wizard.  I created a user for the database and remapped the schema and tablespace.  I get the following errors:

ORA-39071: Value for NAME_EXPR is badly formed.
ORA-01756: quoted string not properly terminated
ORA-39012: Client detached before the job started.

The statement begins, "dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''ABSENCE_DURATION'',  ...
The statement includes a LONG list of tables and the number of characters is 32,659.

I'm thinking that this statement may have exceeded some limit.  If that is the case, how would I break this statement into several statements?

You do not need to enumerate each object you want to export/import, you can pass a query that returns the list:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table tables_to_export(table_name varchar(128 char));

Table created.

SQL> insert into tables_to_export values ('T1');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@o71 ~]$ expdp bp/bp include=table:\"in \(select table_name from tables_to_export\)\"

Export: Release 12.2.0.1.0 - Production on Thu Jun 15 21:18:57 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BP"."SYS_EXPORT_SCHEMA_01":  bp/******** include=table:"in (select table_name from tables_to_export)"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   9.535 MB   76511 rows
Master table "BP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/max/dpdump/expdat.dmp
Job "BP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 15 21:19:19 2017 elapsed 0 00:00:21


Why does this simple select by primary key show such intensive CPU usage? I have advanced performance metrics enabled for my m6g.xlarge MySQL instance in RDS.  I'm noticing that the during CPU spikes (which are expected given our app), that it seems a simple query is occupying the most CPU.
The blue query is:
SELECT * FROM `job_batches` WHERE `id` = "91b0f7a8-0693-4cb4-8647-e7509318fbbc" LIMIT 1 FOR UPDATE

I did an explain thinking that might shed some light on it, but the explain looks fine/efficient to me.

Am I correct in my understanding that this simple query is what's driving a good amount of our CPU usage?.  From a query volume perspective, I would expect to see the same number of SELECT * FROM job_batches... queries as I would say the orange SELECT * FROM channel_subscription.  This makes the blue stand out to me as an area of potential optimization.
The table's architecture is:
CREATE TABLE `job_batches` (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

A possible explanation:

The table is nearly as big as innodb_buffer_pool_size.
The Could did some sort of maintenance just before that spike, hence the buffer_pool (cache) was lost.
UUIDs are terribly inefficient because of how random they are.
Each SELECT (during the spike) found the desired row to not be cached, so it had to read from disk.  (Was there also a spike in I/O?)

If the table is growing is size, the problem will become noticeably worse when the table becomes bigger than the buffer_pool.


Speed up simple MySQL query on large database I have a database with 1 million records and I frequently run this very simple query:

select id from my_table limit 1000000;

It takes around 5 seconds to execute. And if I add more columns to the select it takes up to 10-15 seconds.

Is there a way to speed up this query? I know I select a large amount of data, but it is necessary in my case to do that. Can I speed it up somehow?

I tried to add a index on the columns I include in the select, but there is no difference on the performance after that.

The database is static and the data inside the database will not change.

Since you're reading every record in the table, the database will simply pull everything you want, wrap it [all] up and send it back across the network to your client machine.  The more data you pull, the larger that payload is going to be and the slower it's going to travel.

I frequently run this very simple query
The database is static and the data inside the database will not change.

To me, these two statements makes no sense together.
If the data does not change, then you should read it once, perhaps at application startup, and hold it in the client application.  Don't keep re-reading the same thing over and over again.  That's pointless.
Then there's the question of what you're doing with all this data after you've retrieved it from the database.  I sincerely hope you're not putting it all on a screen in front of a User - a million rows to read through?  No thanks!
I think you need to explain your situation a little more.


dtsinstall.exe does not deploy packages I created SSIS project solution in VS2008.
I created 5 packages and deployed them to SSIS Storage succesfully.
I executed 4 packages successfully.
I leave SQL server few days alone (holiday!).
I open SSIS solution and made little change in 5th package. Save and Build  solution.
I execute an deploy (same way as before, using dtsinstall.exe ..) Change does not apply.
I deleted package from SSIS storage.
I Execute deploy again.. but.. package is missing..

*Deploying is allways successfull

why packages are not loaded to server ?

What am i missing ?

Thank you

DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest

The contents of it look like

<?xml version="1.0"?>
<DTSDeploymentManifest
    GeneratedBy="HOME\administrator"
    GeneratedFromProjectName="SSISHackAndSlash"
    GeneratedDate="2010-04-30T21:19:08.6336677-05:00"
    AllowConfigurationChanges="true">
        <Package>Package.dtsx</Package>
        <Package>Package2.dtsx</Package>
        <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

That file defines the universe of SSIS packages the DTSInstaller will operate upon.

It sounds like the manifest file you are using does not have match the list of packages in your project. You can right click on the project itself and select Properties, Deployment tab and change the value of CreateDeploymentUtility from false to true.

Now when you right click on your project to build, or deploy, your bin\Deployment folder will contain a new version of the manifest file. Double click on that and you should have all the desired packages ready for installation.

An alternate approach, would be to use your manifest file but have PowerShell generate the deploy commands. Advantage of that is you can audit what's being deployed versus the black box of DTSInstaller.exe PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell


Which options to enable to guarantee full recovery in SQL Server Express 2005? I am using SQL Server 2005 Express Edition. I noticed that Recovery Model is set to Simple, by default. I changed this option to Full but want to know which other options to enable to guarantee full-recovery if there is no backup.

Is there any way to take scheduled backups of SQL Server 2005 Express databases?

One answer to add to help explain Recovery models in a nutshell, the terminology (especially with Full also being a type of backup) confuses people.

We'll skip bulk logged which is a hybrid recovery model that is ideal when you are doing certain bulk operations and can programatically change your recovery model as part of the processing.

Quick Background - SQL Server maintains a write ahead transaction log. In the example of your application making an update - the update is written to the log first (and HAS to be written for the transaction to be considered committed). The write will eventually make it to the data files at certain points but it doesn't need to be in the event of a restart/crash of SQL Server. Because it is hardened to the log file, SQL has the right info to recover or "replay" that transaction if it was all done before the crash or roll it back or "undo" the transaction if it wasn't all done. This log file continues to grow and grow and grow depending on your recovery model:

Full Recovery Mode --> You are telling SQL Server "I really care about being able to restore to a point in time." So this means, essentially, that you want SQL to keep it's transaction log records even beyond what is necessary just for that crash recovery but you want to keep the log records, back them up on a schedule and have the ability to restore to a specific point in time. Under this recovery model your log file continues to grow until you take a backup and then it can be truncated (physical size stays where it has grown to but some portion of the log file can be reused based on some other rules)

Simple Recovery Mode --> "I don't need to go to a specific point in time, my last good full backup is fine." You are telling SQL Server to just truncate that log file or reuse the space within it as SQL sees fit - when the records are no longer needed for that crash/restart recovery, basically.

Under either mode above you have the ability to fully recover your database, if you mean - restore it to a known good point. Simple mode means your recovery can only be the last good full backup. Full mode gives you the ability to restore to a specific point of time if your log file is intact at the time of failure or to the last good log backup if not.

So the question is - what are your RPOs/RTOs promised and agreed to with your business unit (http://www.straightpathsql.com/archives/2011/02/can-you-restore-sql-server-sqlu/) to determine which mode. Just remember, if going to full it is imperative that you take transaction log backups or face log file growth that just keeps going. Also remember that when you switch from simple to full, you must take a full backup to initialize your change before you are able to start taking log backups.

More on SQL Server Recovery Models - http://msdn.microsoft.com/en-us/library/ms189275.aspx


Connecting to Standalone/RAC I would like to know if SERVICE_NAME is always configured for a Oracle standalone and RAC setup or it's optional to set up.

In my code I would like to connect to Database using SERVICE_NAME. But if SERVICE_NAME is not always present then I will have to have option to connect using SID. Please advice.

If you're using a current releases, then it will always have default services.  Check dba_services.  Unless there's an application constraint, you should connect with SERVICE_NAME.


Load csv file to mysql using load data local infile I need a simple way to load a csv file from my computer to the database (which is on a remote server).
I'm trying to load the file using a php page that executes this code:

$file = $_FILES['csv']['tmp_name'];
$handle = fopen($file,"r");
$name = $_FILES['csv']['name'];

$import = "LOAD DATA LOCAL INFILE '" . $name .
                "' INTO TABLE temporal_load
                  FIELDS TERMINATED BY ','
                  optionally ENCLOSED BY '\"'
                  LINES TERMINATED BY '\n'
                  IGNORE 1 LINES
                  (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

But when I select a file on the php page and try to load it, shows the error message:

File 'test11-14-15.csv' not found (Errcode: 2)

I already reviewed that the variable mysql.allow_local_infile is ON and the
database connection was made this way:

mysql_connect(HOST,USER,PASSWORD,false,128);

The file is not on the remote server, the file is on my computer, but it should work anyway because I'm using LOCAL.

What am I doing wrong???

Your problem is on using the variable $name on your LOAD DATA LOCAL INFILE statement, you should use the variable $file in your statement..

Your statement should change to;

$import = "LOAD DATA LOCAL INFILE '" . $file. "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());


Why set `group_concat_max_len` below the maximum? MySQL 5.5.28 on Ubuntu 12.04

If the result is longer than group_concat_max_len then the result is gracelessly truncated.

Currently I have a script that tries to check the required length ahead of time and sets group_concat_max_len to be large enough.

But the checking adds extra queries. Is there any downside to just setting group_concat_max_len to the maximum value? The upside is fewer queries.

As per MySQL BOL Here

  The maximum value for group_concat_max_len for 64-bit is
  18446744073709551615

  &

  The maximum value for group_concat_max_len for 32-bit is
  4294967295

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Note: The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

As MySQL documented blog Here Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).

As MySQL Blog by @Shlomi Noach Here group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I'd like it to have a very large default value, in the order of a few MB.

For Further your ref Here & Here


MYSQL after update trigger I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000

CREATE TRIGGER TRG_UPDATE_STATUS
AFTER UPDATE ON product
FOR EACH ROW
  BEGIN
    IF NEW.QuantitySold >= 1000 THEN
    SET NEW.Label = 'BestSeller'
    END IF
  END

Here is the trigger I have.

You can only make persistent changes to NEW in a before row trigger. An after trigger doesn't fire until the record has already been written to the table.

To change the data that will be written to the table, you must use a before row trigger. Changing NEW in an after row trigger is possible, but pointless, since the contents of NEW get written to the table before an after trigger executes, as is explained in the documentation.


remote connection to mysql have two machine PC#1 with db mysql and appache and PC#2 with a c# form try to connect to this db

here is my connection string used in PC#2: SERVER=My_public_ip;PORT=3306;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

when i try to connect locally from PC#1 it work good:

SERVER=localhost;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

i did make my root user remote enabled like that:

`GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;`

and all i get is an Event viewer: unable to connect to any of the specified MySQL host

now i think it has nothing to do with firewall aftr turn them off on both pc and i still get the same error message, but i can't figure it out

You need to create the root user with a password
SET sql_log_bin = 0;
GRANT ALL ON *.* to root@'%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

I would make it a little more secure. Perhaps by doing the following:

use a username other than root (like remote_user)
instead of %, user the netblock of the IP of PC2 (such as 10.20.30.%)

Therefore, you run this grant command  on PC1 instead
SET sql_log_bin = 0;
GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

Note : SET sql_log_bin = 0; prevents recording the GRANT into the binary logs.
Give it a Try !!!
In your question, you said you ran this
GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;

Wildcard characters are not allowed for the user. In fact, the MySQL Documentation on GRANT says

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

Your grant should say
GRANT ALL ON *.* to root@'%' WITH GRANT OPTION;

or
GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;


SQL Server 2005 maximizing performance Our customer has a Microsoft SQL Server 2005 on Windows 2003 Server Enterprise, R2, SP2, 32bit. Running on Intel Xeon X5560 @2.80GHz, with 8 GB of RAM.

Occasionally, the users complained that the access to the server was slow. Although, that might be affected by other factors like workstation hardware, antivirus, etc.

But, I am thinking of maximizing the SQL Server itself.

I read about the "SQL AWE" option which could increase the memory allocation that can be used on the 32 bit OS.

Right now, the AWE is not enabled, I checked using "dbcc memorystatus", and I got result:

Memory Manager:
VM Reserved = 1694984 KB
VM Committed = 1691528 KB
AWE Allocated = 0
Reserved Memory = 1024
Reserved Memory In Use = 0

As I understood correctly, this 8GB of RAM is not being used optimally due to the 32bit OS.

Am I in the right direction here? I meant, if I enabled the SQL AWE, it will improve the SQL performance.

Note that I cannot change the hardware, and I cannot change the OS to 64 bits (not supported by our application).

Thanks.

Occasional end user complaints about "slow" isn't an indication of a SQL Server performance issue.

However, enabling this will improve caching of data but adds some small overhead of managing PAE. AWE requires PAE too.


Transaction log rollback broke Log Shipping chain? Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain?

If this is correct, is there a way to recover from this situation without having to restore a full backup and re-initialise log shipping on the 2nd server?

Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain?

Any backup restored on primary database will break logshipping and you would have to start from scratch to configure logshipping.


Primary keys, clustered indexes and partitioning We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of conflicting advice and resistance, and don't have all the facts to decide on ways forward. Below is essentially what all 4 tables look like.

CREATE TABLE [Example].[MasterRecordTable](
    [rowDateTime] [datetime] NOT NULL CONSTRAINT [rowDateTime] DEFAULT (GETDATE()),
    [recordID] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NOT NULL,
    [field1] ...,
    ...
 CONSTRAINT [PK_MRT] PRIMARY KEY CLUSTERED
(
    [recordID] ASC,
    [date] ASC
)

CREATE TABLE [Example].[ChildTables](
    [recordID] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NOT NULL,
    [field1] ...,
    ...
 CONSTRAINT [PK_CT] PRIMARY KEY CLUSTERED
(
    [recordID] ASC,
    [date] ASC
)

All tables use a clustered primary key that covers recordID and date, even though at no point while processing do we JOIN on or include date in WHERE clauses, and it's only recordID that needs to be a primary key. To me date should not be present in the primary key or clustered index.
All tables carry the date field, is this necessary if you consider my question about archiving/partioning below? The only reason it's in all tables is to assist with our manual archiving process.
We do not make use of foreign keys, and I'd like to know if this is worth reconsidering
We need an archiving strategy. At the moment we have tables with the same structure as above, with _Archive added to the name and placed in a separate file-group and hard drive. We then manually move records WHERE date <= @aYearAgo for each table over to it's _Archive equivalent, daily. This, as well as developing queries that bridge both tables, is tedious and time-consuming. We're busy evaluating partitioning, and I'd like to have some well-informed answers as to what setup is ideal considering the table structure above. It makes sense to partition and archive based on the date value. We'd like to be able to move older data over to progressively slower hard-drives, then deleted after 5 years.
Would it not be more optimal to have a clustered primary key on recordID alone, does the partitioning field (date) have to be apart of the clustered index?
The only time we need date is when we report on these tables. Depending on your answers above, I imagine this can be done best with an NC index on the Master table, which is then joined to the Child tables via recordID.

Please let me know if anything is unclear. This is a dump of the concerns off the top of my head, I'm keen to work with someone knowledgeable on indexes, key relationships, and partitioning. Thanks!

at no point while processing do we JOIN on or include date in WHERE
  clauses

All partitions will need to be touched when the partitioning column is not specified in query predicates.  Put yourself in SQL Server's shoes - how would you know which partitions to access (or not) without knowing the partitioning column value?  Consider that a singleton index seek without partition elimination will seek against every partition.  Compared to a non-partitioned table, this seek overhead will be more noticeable when few rows are returned.  There won't be as much difference with full scans, though.

  Would it not be more optimal to have a clustered primary key on
  recordID alone, does the partitioning field (date) have to be apart of
  the clustered index?

Yes, recordID alone would be more optimal but the partitioning column must be part of the clustered index key as well as all unique indexes.  The implication is that primary key and unique constraints must include the partitioning column as part of the key.

  it's only recordID that needs to be a primary key. To me date should
  not be present in the primary key or clustered index.

Without knowing your data I can't say if date should be part of the primary key or not.  Assuming not from a data model perspective, you cannot partition the primary key index without it.  One approach is to use a non-clustered non-partitioned primary key, considering the performance implications on queries.

Tiered storage for less frequently accessed data is a use case for table partitioning with different file groups.  You will still need to develop a method to either physical move the underlying files or physically move rows from one filegroup to another.  Partition SWITCH alone cannot do the job.