The discipline of Operational and Architectural Management of Database Systems.

learn more… | top users | synonyms

33
votes
1answer
58k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files 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 ...
33
votes
2answers
15k views

What does a DBA have to know about SSAS?

I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management. From the point of view of administering an instance ...
28
votes
5answers
47k views

How do I shrink all files quickly for all databases?

In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, ...
26
votes
7answers
49k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
24
votes
2answers
5k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
20
votes
7answers
900 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 ...
20
votes
2answers
5k 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 ...
17
votes
2answers
113k 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 ...
16
votes
2answers
297 views

Why is it taking longer to create an index after column size increases?

Our vendor changed column widths on almost every column in the entire database. The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before ...
15
votes
2answers
736 views

How to Drop Table with Non-Printable Character on its name

I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific). The non-printable character is beside a underscore, and I've discovered that the shortcut ...
15
votes
4answers
30k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
14
votes
6answers
14k views

Are there any good and free tools for managing a PostgreSQL database? [closed]

Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP. Are ...
14
votes
1answer
1k views

Are there reasons why I should not set my db owner to [sa]?

Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear. Is there any, good reason or circumstance why I should ...
12
votes
3answers
32k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
12
votes
2answers
12k views

Why doesn't DELETE + REORG free diskspace (DB2)?

In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here? The ...
12
votes
5answers
14k 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; ...
12
votes
3answers
7k views

SQL Server 2008 R2 “Ghost Memory”?

We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
12
votes
2answers
40k views

How to create Sql Server login for a domain account?

All, I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I ...
12
votes
1answer
5k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
12
votes
1answer
2k views

Why would you use a managed service account rather than a virtual account in SQL Server 2012?

In SQL Server 2012, service accounts are created as virtual accounts (VAs), as described here, as opposed to managed service accounts (MSAs). The important differences I can see for these, based on ...
11
votes
2answers
8k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
11
votes
1answer
677 views

What does a SQL Server DBA need to know about Windows Server?

Analogous to my previous question What does a DBA have to know about SSAS?, which generated a really wonderful answer (thank you, @ConcernedOfTunbridgeWells!), I now ask a similiar question: What ...
11
votes
2answers
497 views

Which areas of a DBA's knowledge should a developer delve into? [closed]

I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites ...
11
votes
5answers
25k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
10
votes
2answers
2k views

Recovering logical space from a tablespace

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space. A week ago I queried ...
10
votes
2answers
20k views

What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration. I face a lot of problems while setting up mysql master-slave replication. I also face regular mysql replication troubleshooting issues. Can anybody helps ...
10
votes
2answers
704 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
10
votes
3answers
307 views

How to tackle a large undocumented database

I recently got hired as the only IT Guy in a certain Company X and I am tasked to fix their applications, and in my opinion, the best way to start is by understanding the database. Their current ...
9
votes
2answers
2k 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 ~ ...
9
votes
1answer
8k views

ERROR: database “dbname” does not exist

According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using: DROP DATABASE dbname; Or I can drop it using the wrapper tool ...
9
votes
3answers
6k views

Database Administrator vs Database Architect?

In terms of job responsibilities and activities, what is the difference between a Database Administrator (DBA) and a Database Architect?
9
votes
2answers
1k views

MS SQL Server Cumulative Updates - Best Practices

I'm trying to get an idea of what recommended best practices are for the SQL Server Cumulative Updates. Currently, we run on the idea of "do nothing unless an issue fixed by the CU is one we ...
8
votes
3answers
843 views

Accidentally change database name (SQL Server 2008)

It didn't happen to me YET. But I was thinking about this. I was messing around with my training environment, and by mistake I clicked in the database name, and then I touched the letter A. Let's ...
8
votes
1answer
5k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
8
votes
3answers
4k views

Is there an Open Source Metadata Management Solution? [closed]

Is there an Open Source Metadata Management Solution? I'd like to create a Metadata Repository that will hold the details of the metadata of database schemas, tables and data items of hundreds of ...
8
votes
1answer
18k views

What exactly is binding in DB2?

I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our ...
7
votes
2answers
481 views

SQL Server schema intact but every record from every table is gone

We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real ...
7
votes
3answers
2k views

Software Engineers and DBAs [closed]

What are main differences between Database Administrators and Software Engineers? To what extent should a Software Engineer know the details of the underlying database? Where is the border between ...
7
votes
1answer
6k views

Cannot execute as the database principal because the principal “dbo” does not exist [duplicate]

I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012. When I try to access a particular page of my application, I get this error: Cannot execute as the database principal ...
7
votes
2answers
384 views

Automation of backups of Large Number of Database

In a multi-tenant web application where a database is set up for each tenant, the database can be backed up and restored manually through a fairly conventional approach. I am considering this type of ...
7
votes
3answers
290 views

How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( ...
7
votes
2answers
196 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value ...
7
votes
1answer
190 views

On-the-fly query/command modification

I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
7
votes
1answer
5k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as ...
7
votes
1answer
2k views

Mongo Collection `Size` is *larger* than `storageSize`?

I recently compacted my collection using the command: db.<collectionName>.runCommand( "compact" ) And now my collection size seems to be bigger than size on disk! SECONDARY> ...
7
votes
1answer
8k views

Is it possible to synchronize two ms access db?

We have a flower store where we use MS Access and a client program to work with the database. But this program can work only with one database. We are going to open a new store, so now we have to ...
6
votes
3answers
241 views

How do you approach refactoring an ugly procedure/query?

I've inherited a database which contains several procedures which are 1000-1500 lines long, with complex nested sub-selects going up to 7 or 8 levels deep in places. I desperately need to refactor ...
6
votes
5answers
1k views

How do I rule out SQL Server as the reason for a slow website

A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related. What steps or procedures do I take to ...
6
votes
3answers
12k views

How can I enter Mongo as a superuser or reset users?

I'm was playing around with permissions and locked myself out of Mongo database. I'm pretty sure I did this by trying to explicitly add access to a database but instead I overwrote only allowing ...
6
votes
2answers
3k views

Rebuilding index has impact on mdf/ldf files SQL Server 2008

While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files. What does it exactly means? Is complete process done on TempDB and does ...