2
votes
2answers
475 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
6
votes
3answers
6k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
2
votes
2answers
3k views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
1
vote
1answer
49 views

Getting related data from an uncertain column

I am working on a friendship logic, the table friendship has this structure: `friendship_id` INT NOT NULL , `user1_id` INT NOT NULL , `user2_id` INT NOT NULL , `status` INT NULL , `created` ...
5
votes
1answer
290 views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
2
votes
2answers
474 views

SQL Server Trace - Filter on System Processes

Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don’t mean the usual server process (SPID), but the system processes that append “s” onto the end of a SPID (eg ...
20
votes
2answers
2k views

Check existence with EXISTS outperform COUNT! … Not?

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. Yet in several recent scenarios I've measured a performance improvement when using ...
-2
votes
1answer
76 views

How to sync oracle date from internet [closed]

My oracle 10g clock is showing wrong time. When I run SELECT SYSDATE FROM DUAL; it returns wrong date. Please help me how can i update Oracle date/time using internet time.
-1
votes
4answers
761 views

What is difference between tempdb log and database log? In which circumstances these log files uses?

As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log. Please describe me how ...
2
votes
4answers
558 views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
6
votes
1answer
172 views

Database restructure - beneficial?

I have a table for email messages. Then, I have a table that has the message parts. The Parts table contains a field_id, message_id, and data At the time I used Parts to name the table and ...
5
votes
0answers
294 views

SQL Server Sproc calls from IBM Cast Iron orchestration with XML payload fail

I'm hoping that someone on this forum may have experience with an IBM appliance, Cast Iron (Websphere), and how it interacts with SQL Server. I know very little about the Cast Iron appliance, and its ...
1
vote
1answer
233 views

Can I force mysql server to resolve the external ip to localhost?

I'm finally succeeded with ssh tunneling. My point is to open the mysql server to local users only (user@localhost etc.) while providing a remote control to my customers through ssh tunneling. The ...
0
votes
0answers
368 views

Trouble converting MySql Database to SQL Server

I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one ...
1
vote
1answer
293 views

Mysqlbinlog statement reading

I am trying to perform a point in time on a specific database using a snapshot and mysql's transaction logs. I am using the following to pull statements from the time of the snapshot to the time of ...
4
votes
1answer
249 views

How large are blocks of data in SQL Server?

I'm working in SQL Server 2008 R2 and have created a query that gathers and sums the total of data files and log files' sizes. However, I can't find how much actual disk space a single block of SQL ...
1
vote
1answer
615 views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, ...
2
votes
1answer
496 views

Creating Indexed View GROUP BY Epoch Date

I have a few big tables with about 6 billion rows that I was looking to optimize. Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID. ...
2
votes
0answers
385 views

Audit Queries in SQL Server 2008 Including Start Stop Times

I have SQL Server 2008 Enterprise. I know how to set up auditing on the SQL Server, but it looks like the audit logs don't include query start/stop times. We currently use a product called Splunk to ...
3
votes
1answer
548 views

Why does Log Shipping .TRN file copy just stop

I apologize in advance for a long post but I have had it up to here with this error of having to delete LS configuration and starting it over for any DB thats got this error. I have LS setup on 3 ...
5
votes
2answers
2k views

How to determine master in mysql master-slave

I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down). My ...
2
votes
2answers
6k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
3
votes
1answer
102 views

Constraint to one of the primary keys as foreign key

Table1: grid_col (col_id,f_id,f_value) (col_id,f_id) is primary key. Table2: grid (grid_id,col_id,text) (grid_id) is primary key. I want to have a constraint for grid that, col_id ...
-1
votes
1answer
195 views

MySQL 5.6 Delayed Replication - cancel delay questions

I'm using the MySQL 5.6 Master/Slave replication, with Delay set for 2 hours. My questions are : Q1. What is the proper way to cancel the Delay at the slave - i.e. "roll forward" all the changes ...
5
votes
1answer
641 views

IO_STALL question and understanding

I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO. In one 5min period I get a delta of 5826331 ms ...
2
votes
1answer
95 views

Sql Server & NetApp Sql Snap Manager

Does anyone use netapp's sql snapmanager? We are in the process of trying to decide if it is right for us. I know of the more popular features, but am trying to get some real life experience ...
0
votes
1answer
1k views

DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces?

We are in the process of tuning a massive application our company has just written. Part of that tuning includes tuning the databases. We run DB2 LUW on AIX. Specifically we are at 9.7 Fix Pack 4 ...
3
votes
1answer
6k views

Display Monthly Attendance Report in MySql

I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong. I have two tables in my database; one is to store Students records ...
1
vote
1answer
2k views

mysql ibdata1 corrupt

I do not know whether this is a mysql(version 5.6.11) bug, the case is as following. today when i update mysql conf and kill -9 and restart mysqld, i find that it can't restart anymore. after having a ...
2
votes
1answer
578 views

Oracle OEM Database Backup Failure

I am trying to back up an oracle database from OEM, but upon completion the job report says the job failed with the following error: RMAN-03002: failure of backup command at 07/09/2013 10:26:52 ...
0
votes
2answers
2k views

Difference between two recordsets

I'm looking to calculate the difference between two record sets in MySQL. Say I have the following table: colname type attrbutes id uInt PK date date PK count uInt With this data: id ...
0
votes
1answer
175 views

ORA-40341: Access violation on model storage object in Oracle?

While I was trying to drop a table, it throws following error in Oracle SQL Developer: ORA-40341: Access violation on model storage object The tables are temporary tables created while pushing into ...
2
votes
1answer
1k views

Invalid object name 'master.dbo.MSreplication_options' when restoring DB

I've tried Googling for an answer to this but have got nowhere. I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase. I have a second ...
0
votes
0answers
146 views

Efficient, up-to-date way to import an Oracle database into MySql

Here's the situation: I need to automatically merge data from an Oracle and a MySql server into a single, new MySql server. The merging will happen daily, and the resulting server will be hosted on a ...
2
votes
1answer
153 views

mysql binlog file size stop growing

I made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write ...
0
votes
1answer
99 views

Database design in MySQL

I am designing a database for an web application, in this system there are multiple users. Consider, Public (Buyer). Merchant. Tele Marketing Executive(TME). Marketing Executive(ME). Lead ...
3
votes
1answer
735 views

Teradata : Disadvantage of Collect stats

I want to know any disadvantage can occur by doing collect stats on a column that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 ...
2
votes
3answers
846 views

Mysql datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system... Is it fine to have the username column as ...
5
votes
1answer
482 views

How does Berkeley DB manage its files?

I'm using Berkeley DB (BDB) as a persistent store for a JMS queue. When I consume an entry from the queue the underlying BDB files do not immediately shrink, but do eventually. I'm running into ...
2
votes
2answers
73 views

Lock hints on preconditions

Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data? (This is a ...
0
votes
1answer
47 views

Postgresql: No data left on drive, how can I wipe it and start over?

I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files. I would like to just ...
3
votes
1answer
437 views

Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

I created a Extended Event to monitor and tune a Selective Xml Index CREATE EVENT SESSION [SelectiveXmlIndexing] ON SERVER ADD EVENT sqlserver.selective_xml_index_no_compatible_sql_type, ADD EVENT ...
1
vote
0answers
286 views

Can SQL Server 2008R2 SSIS packages access SQL Server 2012 databases with driver updates only?

We have two products running on SQL Server that we will be upgrading to SQL Server 2012 on a staggered schedule. Product B uses SSIS packages (using ODBC drivers) to extract data from Product A's ...
0
votes
1answer
74 views

Indexes and Optimizations for Logging System

I'm designing tables for a new log system and am having trouble figuring out what indexes I'll need (I'm a developer, not a DBA), and am also open to any suggestions/criticisms/etc. for making the ...
3
votes
0answers
163 views

SUSPECT in mdf files

When I run ALTER DATABASE EMS102013 SET SINGLE_USER; DBCC CHECKDB (EMS102013) WITH NO_INFOMSGS, ALL_ERRORMSGS shows message Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was ...
1
vote
0answers
513 views

Mysql raising intermittent “system error: 32” errors on connect

I'm running mysql 5.6.12 on OSX 10.8 and getting an error like this one constantly: ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32 ...
1
vote
0answers
44 views

MySQL - Randomly long DESCRIBE queries

I have several queries like this (running on MySQL 5.5): DESCRIBE `TABLE`; And it randomly takes 5-10 seconds on my production servers. We have an ORM that caches this data once per day (per web ...
0
votes
1answer
333 views

RTF to Plain-Text? [closed]

Did you ever get the library from this post? We have a need to convert whole, large, whole tables worth of RTF information into something usable and would rather not reinvent if possible. Converting ...
4
votes
1answer
626 views

SqlPackage does not pick up variables from profile

I want to upgrade a database using .dacpac and sqlpackage.exe here is how I run sqlpackage: SqlPackage.exe /Action:Publish /SourceFile:"my.dacpac" /Profile:"myprofile.publish.xml" The ...
1
vote
1answer
663 views

Partitioning vs Indexes

I'm very new to databases, so I apologize if my question is very basic... Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in ...

15 30 50 per page