The discipline of Operational and Architectural Management of Database Systems.
3
votes
1answer
77 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
0answers
38 views
Database Timeout Error from VB Application
Recently we are receiving so many database timeout error from different servers
we are using vb application -- which will use ODBC connection to connect database - here no problem
when we run the ...
1
vote
1answer
30 views
Adding users to a list and sort by category?
I'm using SQL-Server 2012 and I would like to know how to solve this program.
So:
I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to ...
0
votes
1answer
58 views
How to deny access to see the logins in a database
I create a login in SQL Server 2008 R2, and when that user connects to a database, it can see all the logins in the security logins. Can I deny access to watch the logins of the database?
1
vote
3answers
235 views
Instance Startup Modes & Shutdown Modes
So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN.
What I want to know is in NOMOUNT mode it is stated that the instance started but still ...
0
votes
0answers
38 views
Prices for database administration tools [closed]
What should be the optimum price of an SQL database administration tool with 16 features including backup status review, server and data configurations, server statistics, disk space review, ...
2
votes
0answers
56 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
0
votes
0answers
74 views
DBA group in Linux
When we need to install Oracle database in Linux then the user should be in dba group. What is this group and why the user should be in this group for installing Oracle database. What happens if the ...
1
vote
1answer
67 views
Insert from one row to another using cases
My original table was(being used since 2005):
CREATE TABLE `request` (
`msg` VARCHAR(150) NOT NULL,
`id` VARCHAR(20) NOT NULL,
`ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
`date` ...
6
votes
1answer
190 views
Role of Database Administrators
I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the ...
3
votes
1answer
116 views
SQL Server Restore from one database to another
One of our devs backed up a dev database, and then restored it in production. It's a new database for a new app that was deployed last night intentionally to prod.
Now in the backupset table ...
0
votes
2answers
133 views
Secure Linked Server - Non privledged user possible? Registry corruption?
Is it possible to use a non privledged Windows domain account to impersonate itself in a linked server?
And why would it be unable to read the registry for available network protocols?
Overview: ...
1
vote
2answers
169 views
SQL Server 2012, restore database
I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server)
In my new database, the triggers are missing. Why did I ...
8
votes
2answers
1k views
Handling growing number of Tenants in Multi-tenant Database Architecture
Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way ...
7
votes
2answers
734 views
100 TeraBytes Capacity Database - Resources and Time Estimates
I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment:
Storage Capacity ~ 100TB
Tables ~ ...
1
vote
1answer
66 views
Create database on new partition
I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package.
I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
0
votes
0answers
37 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
0
votes
0answers
57 views
Stored Procedures and Functions in MySQL
I just need to know about Stored Procedures and Functions in MySQL.
While I designing a database for an eCommerce website, I was in a situation to write stored procedure for my database, this is new ...
1
vote
3answers
146 views
Database Design Relationship for Patient Visit in MS Access
I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.
There are a total of 6 ...
6
votes
1answer
86 views
What is a good way to determine the performance I should expect from a PostgreSQL server?
Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
6
votes
4answers
3k views
Why using innodb_file_per_table?
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
4
votes
0answers
69 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
4
votes
2answers
85 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
1
vote
1answer
187 views
Images, PDF, audio and video files in MySQL
I am designing a database for an eCommerce website.
How can I store images, audio, video and PDF files which are uploaded from the online users, in a MySQL database?
I am using the InnoDB storage ...
2
votes
0answers
72 views
How to properly configure pgBouncer transaction level pooling?
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
2
votes
0answers
110 views
Create Oracle ASM Disks Without Installing Oracle
I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am ...
2
votes
2answers
527 views
Cannot login to Oracle11g with System username
I just installed Oracle 11g into my machine.
I was about to shut down a database instance, and Oracle asked me for username and password, I provided the credentials I used to login into the web ...
3
votes
2answers
87 views
Two corrupted indexes found by DBCC CheckTable
I'm wondering if this problem is solvable by dropping/recreating the two indexes. Both are rarely used, nonclustered, non PK, non FK indexes.
Here is the dbcc output:
Table error: table ...
7
votes
2answers
19k views
oracle - list users with access to certain tables
I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
1
vote
2answers
85 views
An admin (GUI or shell tool) for managing all kinds of databases
I needed an interface/admin (web or desktop) from which I could manage all the databases in my system. Currently I use separate admins for each (phpmyadmin for mysql, pgadmin/phppgadmin for postgres, ...
5
votes
1answer
405 views
Stored Procedures Best Practices: Fenced or Unfenced?
I believe I understand the reasons behind fenced and unfenced stored procedures.
Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
0
votes
1answer
68 views
What is the maximum number of rows in a clustered index on datetime column?
I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
16
votes
7answers
389 views
Where can I find some guidance on index strategies?
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.
As a general rule, which fields should be indexed?
Which fields should ...
1
vote
2answers
101 views
Database Is In InRecovery Mode.
I have a database that has entered "Recovery Mode". It was working fine, but ended up in this status a few days ago.
I have been told that a cause can be the partition holding the datafiles running ...
0
votes
1answer
119 views
SQL Server primary key create clustered index
I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.
If I perform an alter table to assign a primary key will this ...
3
votes
1answer
404 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
2
votes
1answer
195 views
How to grant permission for am administrator account in SQL Server 2012? [closed]
I installed SQL Server 2012 on my laptop. I am trying to run a script to create a simple database.
SQL Server version: 11.0.3128
SSMS starting parameters:
server type: database engine
server name: ...
4
votes
2answers
172 views
How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?
The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages ...
4
votes
3answers
9k views
Oracle 11g - Create Database fails?
I've just installed Oracle 11g and linked it to SQL Developer.
I want to create a database for my django install.
CREATE database 'django';
Error starting at line 1 in command:
create database ...
31
votes
11answers
2k views
How could DBAs be more 'programmer friendly'?
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
0
votes
0answers
45 views
400GB Innodb table with deletes and no data_free?
I have an Innodb table which is over 400GB and performing quite a lot of deletes on it, so I thought I should try to optimize the free space kept it is probably creating. I ran this query to find out ...
2
votes
3answers
1k views
Unable to start SQL Server Instance 2008 R2 - DB file corrupt
I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is
" The log scan number passed to log scan in database ‘master’ is not valid. This ...
5
votes
1answer
6k views
Is there any best way to reduce the size of ibdata in mysql.?
I have some Production servers whose ibdata increases in size day by day.
It has already consumed 290GB of space.
The tables in the servers are mostly InnoDB and there are high read and write ...
1
vote
3answers
306 views
Detecting SQL Server Utilization with a query
My current project will send queries to an sql server constantly and It may use 100% of the memory or CPU.
How can I check if the server is nearing full utilization in a stored procedure so that I ...
0
votes
2answers
154 views
Restore the latest backup on the database
We have a stored proc which takes the backup from a database to the specified location. Sometimes the backup process runs more than once. It causes having more than one backup set on the same file ...
1
vote
0answers
78 views
SSMS produces error if query returns too many rows
We are having an issue with a server that we linked to our sql server 2012 instance.
The server that we linked through an odbc or oledb connection is Pervasive SQL.
Selecting from the linked server ...
0
votes
2answers
206 views
Remote database connection problem?
I have two systems with Windows XP. I have installed SQL Server 2005 on a system and tried to use SQL Server Management Studio Express on both to use the database.
I am able to use the database on ...
0
votes
1answer
159 views
mysql fine tuning: open_tables, opened_tables
I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors; I've managed to reduce the ...
