14
votes
6answers
9k 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 ...
2
votes
3answers
1k views

How do I get % progress for “type db.sql | mysql”

I am doing: type db.sql | mysql and the console output is nothing. I see the ibdata1 file growing, but I don't know how many GB it will grow to. I would like to see a % progress indicator of some ...
2
votes
1answer
12k views

How do I specify that a column should be auto-incremented in pgAdmin?

I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application. If I create or have created a table with pgAdmin III, how can I add ...
3
votes
1answer
364 views

MySQL Config on Mac

I have a MacBook pro with 2.53 GHz Intel Core 2 Duo and 4GB DDR3 ram. I am running a huge database on my laptop with over 100 million records. I want to know : Which of the .cnf files is suitable ...
2
votes
2answers
4k views

In Oracle, how to globally enable parallel execution?

In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
4
votes
2answers
748 views

Best Setup for Database Cluster

I need to set up a good database clustering solution for a system that I am putting into production. I was hoping to get some advice from the experts as I am a primarily a developer and not a database ...
1
vote
1answer
12k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
3
votes
4answers
496 views

Regnerating an execution plan - is this a valid approach?

If I am optimizing a query, in order to ensure that this query does NOT uses cached execution plan I use OPTION (RECOMPILE) with the query. Is it a good approach?
47
votes
4answers
61k views

How to safely change MySQL innodb variable 'innodb_log_file_size'?

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB: ...
3
votes
2answers
2k views

Keeping “SQL Trace was started” and “SQL Trace stopped” from filling up my SQL logs

I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data). The problem is that my SQL ...
2
votes
2answers
6k views

Execute PL/SQL script block in C#

I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language I am using is c#, the DB is oracle10g What I am actually doing is the following : //ConnectionSting ...
4
votes
1answer
526 views

What can be done, if anything, to tune a SharePoint database?

SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in ...
3
votes
1answer
428 views

MySQL Database Schema for Volleyball

I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round. Teams Table, say IDs 1,2,3,4 Fixtures Table, Round No.1, Team ...
5
votes
3answers
1k views

Is the ADO.NET Entity Framework known for generating poorly performing queries?

This question is inspired by the comment posted to the latest ServerFault blog post: Are you guys still using LINQ to SQL? I know I can use SQL Server Profiler and/or the ToTraceString method to ...
4
votes
4answers
1k views

Best method to give two Oracle databases on the same server access to common data

With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases? It is possible to put these tables into a third ...
6
votes
3answers
1k views

How do I rename my local SQL development server?

I'm a dev who inherited a mostly functioning box doing most of what I need. Except for the machine name is still that of the old dev (we name it "{username}-dt" or "{username}-lt" for ease of id on ...
21
votes
3answers
16k views

How do you calculate mysql max_connections variable?

How do you calculate mysql max_connections ? What do you take into consideration ?
0
votes
1answer
1k views

detaching database results in error “SQL Server detected a logical consistency-based I/O error”

I'm trying to detach a database and I'm getting this error : SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0). It occurred during a read ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
3
votes
3answers
150 views

Is it possible to install Developer Edition of SQL Server as the primary development server?

Is it possible to install Developer edition of SQL Server on a Windows server environment as development server? Each developer will have a local copies of same SQL Server edition and will connect to ...
3
votes
3answers
5k views

AWR Report - Tuning Database

We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait ...
5
votes
1answer
360 views

Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

I just created a UDF and testing it I found only works when I use this syntax SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132) I was expecting it to work like this: select * from fnIsReportingTo ...
5
votes
2answers
1k views

How do I replace this where clause with a join?

Typically when I see SQL that uses something like select * from employees where epmloyeeTypeId in (select id from type where name = 'emp') I replace the where with this: select e.* from ...
4
votes
2answers
5k views

Help solving a Clustering Issue: [sqsrvres] ODBC sqldriverconnect failed

I have a 2 node cluster (NODE-A & NODE-B) with 2 SQL instances spread between them. INST1 prefers NODE-A, INST2 prefers NODE-B. INST1 started generating errors then, failed over to NODE-B. ...
7
votes
3answers
560 views

Data Warehouse Creation

I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me. Situation We have a Cobol "database" (I ...
4
votes
4answers
376 views

Where can I find good literature for beginners?

I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. ...
9
votes
3answers
386 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
4
votes
2answers
474 views

How can I disable async IO or isolate one session that slows or freezes other sessions?

Each day I have a session that produces Avg.Disk Queue Length > 800 (5min interval). I have found that just before this moment Sql Server optimizer have made an error: it decides to use a ...
5
votes
2answers
7k views

Why do we need to backup Oracle archive logs along with datafiles

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that Archived redo logs are the key to successful media recovery. Back them up regularly. But, I wonder, why backups of ...
2
votes
1answer
563 views

truncate table for all db in sql server 2000

i need a script that will go to every db in server and if exsistes a table named "table1" will do truncate table table1
5
votes
2answers
1k views

After I kill my clients, MySQL continues to process queries for hours

Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second. When the client has been running for hours, and I terminate ...
2
votes
1answer
636 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
5
votes
2answers
1k views

MySQL: What is the maximum number of cores used by a single MySQL server?

What is the maximum number of cores used by a single MySQL server in a production setting?
4
votes
4answers
2k views

Examples of SQL transaction procedures for sales tracking or a financial database

I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions: salesman creates new product ...
1
vote
1answer
356 views

How can I pivot this select?

SELECT sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText FROM StatusCode sc LEFT OUTER JOIN ProjectOverview po ON sc.CID = po.ProjectOverviewCID AND po.ProjectId ...
2
votes
2answers
73 views

Research Database: Access and Ownership Policies

I'm a PhD student in an academic research laboratory. One part of my project involves collecting samples and generating data. Another part involves establishing a research database to store the ...
1
vote
1answer
110 views

a query that listing all rows and sizes of tables in a Database

I am using SQL Server 2005.My question is;I am looking for a query that listing all rows and sizes of tables in a Database.
4
votes
3answers
46k views

Oracle SQL - CASE in a WHERE clause

Is it possible to somehow do this? WITH T1 AS ( SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL SELECT 3 AS SEQ, ...
3
votes
2answers
133 views

I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice. Debates on platforms and stability aside, the evidence suggests ...
13
votes
6answers
6k views

Is it okay to put dots in SQL Server database names?

If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?
1
vote
1answer
2k views

automating a database backup, restore and stored proc call

How do I create a process that will: backup Database A restore it as Database X (needs to be definable) then run a stored procedure against Database X. shrink and truncate its log file. Anybody ...
3
votes
1answer
441 views

Any reasons at all *not* to use MySQL in this scenario

I want to know if there is any reasons at all not to use MySQL in this scenario. 2 servers behind a virtual IP with MySQL master-master replication Goal: redundancy, want this to run 24/7 ...
10
votes
6answers
2k views

SQL Server command line usage? Is it possible?

I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's ...
3
votes
3answers
3k views

cursor: pin S wait on X in 11g

This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution. Any advice will be ...
5
votes
3answers
21k views

How to find the actual space being consumed by indexes on a table in Oracle?

I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be ...
5
votes
3answers
1k views

Alternatives to an INSERT Trigger

Looking for an example code in Oracle to do the same. i.e. Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like? ...
5
votes
3answers
231 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 ...
3
votes
3answers
120 views

Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server?

Latest version of Management Studio (i.e. the one that ships with SQL Server 2008) finally has a Transact-SQL IntelliSense feature. However, out-of-the-box it only works with SQL Server 2008 ...
3
votes
2answers
1k views

How can i detect whom a person reports to using a self-referencing employee table?

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect ...
1
vote
3answers
189 views

How do I normalize and transform field text algorithmically

I've got names and emails exported from one sales system that I am trying to move into my email service provider. I have to normalize the data. The email field has data like this: John Smith ...

15 30 50 per page