All Questions

Filter by
Sorted by
Tagged with
3
votes
1answer
2k views

Replication slows down on massive deletes

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue. We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...
7
votes
2answers
4k views

Will transactional replication push a CREATE INDEX immediately? Or do I need a snapshot?

I have transactional replication set up on SQL 2008, and the article in question has "Copy nonclustered Indexes" set to TRUE. If I add a non-clustered index on the publisher (using CREATE INDEX), ...
11
votes
2answers
515 views

@table_variable or #temp_table

I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
1
vote
3answers
2k views

Undo DDL statement on View in Oracle

I Want to undo DDL changes on View, how to do that? I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If ...
26
votes
2answers
18k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
2
votes
2answers
933 views

Can a database file be brought back online if the file is recovered from a disk backup?

TL:DR Deleted .ndf files Database wouldn't load Set deleted files offline Brought database online Restored files from disk backup How can I bring them online/remove the database reference? During a (...
8
votes
1answer
2k views

How does table compression affect SQL Server's buffer pool?

I'm curious as to how SQL Server's buffer pool is affected by compressed data tables. Is the data in memory compressed, just as it is on disk, or is it fully decompressed? If the data is stored ...
5
votes
5answers
3k views

log shipping to multiple-read servers

The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section: Benefit from log shipping, which helps automatically back up data on separate ...
5
votes
1answer
1k views

What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we don't use other features of pgpool like Replication, Load Balancing, ...
1
vote
2answers
3k views

Is the mapping between PostgreSQL system tables and information_schema views documented?

If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog). I'm looking for documentation ...
2
votes
1answer
24k views

What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g?

I am ready to upgrade the oracle 10g prod db to 11g, but we have 130 invalid objects in SYS (all are belong to JAVA CLASS object_type). And from the best practice of oracle upgrade from 10g to 11g, it ...
8
votes
2answers
2k views

SQL Disk Setup Advice - TempDB, Log DB, Data file placement question

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
3
votes
2answers
145 views

Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?

I know this may sound ridiculous but: Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server. I think it will ...
1
vote
1answer
5k views

12.0 Access Database Engine OLD DB Provider Not showing up in SQLEXPRESS 2008

I am trying to create a linked server to an 32 bit 2010 access database inside of SQLEXPRESS 2008 R2 64bit. Everything I've read has pointed me at the download : Access Database Engine I've ...
4
votes
2answers
433 views

Data Model For Summarizing Student Info

I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance ...
9
votes
2answers
2k views

How to best store Google ngrams in a database?

I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database. I am wondering how one ...
2
votes
1answer
2k views

Intermittent Disconnection to DB on SAN. Event ID 17053 and 9001

I am getting some strange issues with our MS SQL 2008 database where intermittently we lose connection to the db for 10+ seconds or so. The db is stored on a SAN over fiber. The latest event log ...
2
votes
1answer
183 views

SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files?

As far as I read msdn there is no variable that corresponds to the -b flag of the command (on error batch abort). I found no to enable this mode inside a script when running in sqlcmd mode. Is there ...
4
votes
2answers
3k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
8
votes
2answers
6k views

How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?

How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!
0
votes
1answer
3k views

Materialized views master table

New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by ...
4
votes
1answer
2k views

What is SQL/PSM and how does it differ from other versions of SQL?

I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL ...
0
votes
1answer
169 views

Add parent_id dynamically based on another field

I have a version table using a field to track order: id, owner_id, order, version 5, 1, 1, 1.1 6, 1, 4, 1.2 7, 1, 7, 1.2.1 8, 1, 10, 1.3 I want to ALTER TABLE version ADD COLUMN parent_id and have ...
3
votes
2answers
1k views

What is the best way to mirror an ASPState database?

I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me ...
2
votes
1answer
247 views

What are hypothetical indexes?

They seem to be created by tools like DTA. For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.
3
votes
1answer
112 views

Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column ...
4
votes
1answer
17k views

PostgreSQL: Change log file location

How can I change the location of the log file in PostgreSQL? Can it be done without stopping the cluster?
11
votes
2answers
835 views

Is sharding effective for small collections?

It looks like database sharding is great if I have huge collections. What if I have lots of fairly sized collections ? Let's say that for 1 collection of 100 000 000 documents (not very big comments) ...
3
votes
2answers
1k views

SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server. This is how I intend to do it: Analyze the stored procedure for any bad TSQL practices (like count * etc) Run it ...
0
votes
1answer
491 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
4
votes
3answers
588 views

Usage of SQL Server 2008 R2 Express. Please Advise

I'd like you all to give me hints and advise on the following scenario: We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application. The Web Application won't have ...
2
votes
2answers
4k views

Corrupt InnoDB tables after every mysql restart. What's wrong?

Recently I changed a few settings in my mysql configuration. I did the following: - stop mysql - save the changed settings - delete all ibdata and ib_logfiles - start mysql Normally this works fine ...
12
votes
3answers
10k views

MySQL data type for 128 bit integers

I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers. Right now, I'm using binary(16) but that involves a lot of conversion ...
2
votes
2answers
3k views

Is it possible to query uncommitted data of a different session? [duplicate]

For interactive debugging purposes I'm wondering whether it's possible to query the data another session has currently put into the database but not yet committed. Say I have an my application open ...
41
votes
3answers
47k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
23
votes
5answers
1k views

Should Query Tuning be Proactive or Reactive?

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best ...
0
votes
2answers
4k views

What will be the best data model for membership based websites?

I am building a paid membership based website where user can signup for different kind of membership. Membership will essentially enable or disable certain pieces of functionality in the website. A ...
2
votes
2answers
203 views

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
4
votes
3answers
5k views

How? SQL Cluster with multiple active nodes (not failover)

I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN. Instead of building a failover cluster (which I have done), I would instead like to setup several nodes ...
3
votes
1answer
106 views

Are database backups specific to the OS on which they are created?

I am trying to get a better understanding of the behaviors of database backups for Sybase (15.X) and Oracle (11.x) as it relates to the underlying OS. Is the backup file OS specific or can it be ...
16
votes
1answer
22k views

How do you ORDER BY a parameter?

Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!). Basically I have ...
7
votes
3answers
3k views

Online resource for writing Access SQL statements [closed]

I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, ...
5
votes
5answers
3k views

MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

I have a slow query log item with the following: Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739 No one in my group can explain it besides a guess about packet loss. Is there a ...
16
votes
1answer
2k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
1
vote
1answer
3k views

V$SQL table elapsed time

Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?
2
votes
1answer
1k views

Using the special Dual table to read sequences

It there a performance impact when using the dual table to read a sequence' nextval over an direct insert? I'm currently optimizing the performance in a large IT system and what I've found is that ...
10
votes
2answers
8k views

PostgreSQL: Last access time to table

I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed. What's the best way to check when was the last time a certain table was ...
4
votes
2answers
2k views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
2
votes
1answer
344 views

Grant access for any logged in user for common database

In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login. I need a way that ...
6
votes
2answers
440 views

Row padding to get more performance

I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance: Because rows are small (many fit a page) multiple locks may compete for one ...

15 30 50 per page