7
votes
1answer
9k views

MySQL column defined with NOT NULL is allowing nulls

I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, ...
8
votes
6answers
7k views

SQL Query Formatter [closed]

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
4
votes
1answer
355 views

sp_sqlagent_get_perf_counters SQL Server 2005 running every 20 seconds

A little background info: We are running SQL Server 2005 SP2 on a server with Microsoft Windows Server 2003 SP2. When I run profiler I notice that EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters is ...
8
votes
1answer
764 views

How to get accurate query performance?

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...
3
votes
1answer
928 views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
8
votes
5answers
3k views

How can I profile SQL Azure?

I am writing a web site that uses SQL Azure heavily. However, it is painfully slow. Is there an easy way to profile the live SQL Azure instance?
3
votes
4answers
2k views

SQL Server 2008 R2 with huge amount of transactions

I am currently developing a project with a huge amount of database transactions. My estimate is around 70,000 inserts/updates/deletes/selects every 2 minutes, since I am not there yet, I can only ...
8
votes
2answers
4k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
4
votes
3answers
5k views

Disable trigger for just one table

Is it possible to disable a trigger momentarily but for just one table. For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
5
votes
2answers
28k views

Grant table privileges to a user connecting from any host

I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
3
votes
2answers
8k views

MySql - changing innodb_file_per_table for a live db

I have a large MySql DB (150GB) and only now i've noticed that the innodb_file_per_table is set to off which cause the entire DB to be hosted on one single file (ibdata1). i want to active ...
1
vote
1answer
767 views

SQL Server 2008R2 Logon failed

The SS error log indicates the following at a frequency of 2 to 3 times per hour: Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution. [CLIENT: ] There is no logon ...
1
vote
2answers
740 views

All columns of table should be indexed or not mysql database?

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
3
votes
3answers
2k views

sqlagent job - using system folder path with CmdExec

I'm trying to setup a sqlserver agent job of type CmdExec for running an executable inside %programdata%\my company folder. But if I use %programdata%\my company\myexec.exe, the job fails to start ...
3
votes
2answers
2k views

TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

I've got an issue with SQL Server. The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers. This happens randomly, once or twice a week, ...
4
votes
2answers
4k views

List primary keys for all tables - Postgresql

Is there a query that will do that? I found some queries that can do this for one table, but I wasn't able to modify it so I can see: tablename | column | type
10
votes
1answer
8k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
0
votes
1answer
2k views

how to make a table unlocked within transaction block

I am using SQL Server 2008. I have several stored procedures that process different reports. Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default ...
3
votes
3answers
572 views

Restoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation?

I need to read some data from a database given by its backup file. So, I issue the RESTORE DATABASE command, which creates two files - the database MDF file and the log LDF file. Is it possible to ...
1
vote
2answers
1k views

Dropped table not showing up in recycle bin

I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it . What's the ...
3
votes
1answer
1k views

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777). When i tried to open the file ...
1
vote
3answers
2k views

SQL Server 2005 Restore from Full and Differential Backup

I'm not a DBA, however I have been a developer for 10yrs and am competent. I wish to move our corporate application to a hosted service and I need to copy the data. The data centre is too far from ...
0
votes
2answers
196 views

New PostgreSQL 9.1 configuration doesn’t work

I changed the length of table names in PostgreSQL in the file pg_config_manual.h (in C:\Program Files\PostgreSQL\9.1\include) to 128. Then I reloaded the new configuration by selecting menu "Reload ...
1
vote
2answers
707 views

Mysql master-master permission replication issue

I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication ...
7
votes
1answer
927 views

Mongo Map-Reduce or Sharding?

So I asked this question: Setting up Mongo with clustering But I didn't know enough until I read the answers and did more research. Is it better to use Map-reduce or sharding for fast-paced ...
3
votes
1answer
6k views

Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
1
vote
1answer
122 views

Sybase internal tables for ORM generation

I am working on a Perl application that uses DBIx::Class::Schema::Loader in order to generate the ORM layer of the application, against a Sybase ASE 15.5 database. We recently migrated from Sybase ...
1
vote
3answers
9k views

SQL Server 2008 R2 Query Last Successful Database Backup For All DBs

Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?
6
votes
1answer
6k views

Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it. ...
4
votes
2answers
189 views

Deadlock during Slony uninstall and concurrent access of DB

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process. I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
6
votes
3answers
853 views

Why is @temp table performance some times worse than #temp table performance?

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this: declare @tempTable table ( ... ) insert into @tempTable select ..... to ...
6
votes
4answers
9k views

When to add indexes to #temp tables?

When testing stored procedures in SSMS, it sometimes says there is a missing index on #someTempTable____________________000000000000005B] (someField) etc etc When I do add them like this to the sp: ...
17
votes
3answers
2k views

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
2
votes
4answers
381 views

What “tool” to use for monitoring instances and where to store this data?

I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server ...
2
votes
2answers
94 views

Is it safe to use “Safety” as a schema name?

I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
5
votes
3answers
1k views

Does the PCI standard forbid the use of Windows Authentication?

We currently use Mixed Mode for our SQL Server Authentication. I've tried to convince our DBA to allow us to use Windows Authentication so we can use Team Foundation Server, however he absolutely ...
4
votes
1answer
640 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
5
votes
2answers
525 views

How can I run multiple concurrent versions of my application against the same database?

Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of ...
7
votes
1answer
1k views

recover a single mysql database on a busy master-slave replicated system

Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system. I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
2
votes
2answers
269 views

Sudden reduction in full backup size

I recently repaired a single corrupt page in one of the tables in a 70gb database. I had a backup which included the data contained in the page so I went about using DBCC CHECKDB with ...
2
votes
1answer
3k views

SQL Server 2008 R2 64bits repair error

I had to reinstall the default server instance on one Windows 2008 R2 server. I checked that the sql groups were cleaned and the registry keys cleaned before reinstalling. After reinstalling, the ...
0
votes
2answers
1k views

Creating a New Database Across a Network with SQL Server 2008 R2

I appreciate that the above should not be done ('Creating a New Database Across a Network with SQL Server 2008 R2') for all of the reasons outlined here but a client wants to know how to do this and ...
14
votes
3answers
10k views

Meaning of 'SET' in error message 'Null value is eliminated by an aggregate or other SET operation'

I saw the above 'ANSI warning' message today when running a colleague's script (and I don't know which of the many statements caused the warning to be shown). In the past I've ignored it: I avoid ...
4
votes
2answers
2k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
25
votes
2answers
8k views

I/O requests taking longer than 15 seconds

Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
1
vote
2answers
1k views

Can I restore the defult SQL Server Public Role?

I setup a new user login and attached it to a specific database on my server. I then opened up management studio on my local pc, connected to the database using the server IP and selecting the ...
5
votes
1answer
979 views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
2
votes
1answer
196 views

Varchar to date in SQL Server 2008 [closed]

I have varchar data in database table which stores date in DD-MM-YYYY format. I want to select that varchar in MM-DD-YYYY format. I'm using select convert (varchar(30), (select CONVERT(datetime, ...
3
votes
3answers
516 views

Odd SQL Server hang when querying exec DMVs

The other day I ran into a very odd issue that when I execute this query: select * from sys.dm_exec_cached_plans cp inner join sys.dm_exec_procedure_stats ps on ps.plan_handle = cp.plan_handle ...
3
votes
2answers
9k views

Oracle 11g SYSDBA login

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to ...

15 30 50 per page