[how to] IO Frozen in error SQL error log |
- IO Frozen in error SQL error log
- Postgresql output doubt
- Foreign Keys with Composite Key
- Find out number of active locks on a table
- SSIS Expressions Date Format get just YY for year
- How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?
- Bitmask Flags with Lookup Tables Clarification
- Slow Memcached: Average 10ms memcached `get`
- SSIS SQL Result to tab delimited txt file
- Ubuntu / MySQL / Percona XtraBackup gives error "sh: 1: xtrabackup_55: not found"
- Setting DATEFIRST to 6
- SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
- UPDATE / SELECT incredibly slow in MySQL InnoDB
- How to suppress an informational raiserror message
- BCNF when no keys are available
- Using Oracle hierarchical queries for transposing field value into rows
- Can I use replication/mirroring to have a "Current" database and a "Full History" copy?
- MySQL slave stuck in "Reading event from the relay log"?
- Real Time data collection - Help select Database
- How do I demonstrate Transaction Log activity?
- sql server partitioned view execution plan questions
- BIT columns all "1" after a phpMyAdmin export/import
- Moving SQL Server to different domain
- Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
- Handling concurrent access to a key table without deadlocks in SQL Server
- Slow query performance
- How to modify an update in Oracle so it performs faster?
IO Frozen in error SQL error log Posted: 01 Apr 2013 07:38 PM PDT The log shows IO DBName is Frozen follow by thaw but yet my database schedule backup task which normally been running isn't since the network team are doing backs (not database backups, according to them) on the database server, using ArcServ 16.5. They run their backup utility around 11:00pm or later And my maintenance backup runs at 2:00am. This is in a 2000 SP4 environment. I would like to gather much evidence to show them that their utility is cause my schedule backs not to run. |
Posted: 01 Apr 2013 05:48 PM PDT I am getting output and I have to press spacebar after one page of output, I want to get rid of that, how can I and which option I have to enable. By automatically, how can I automatically execute all commands saved in a file when I logged in psql via terminal? Thanks in advance. |
Foreign Keys with Composite Key Posted: 01 Apr 2013 05:26 PM PDT Imagine I have a table with the following dependency:
Where the three attributes in bold form a composite primary key for the table. Now then, I want to link (form a relationship) from another table to this one. How do I do that? If I had an ID column I'd know what to do, but I've never come across a scenario like this. Would I have to add all three columns |
Find out number of active locks on a table Posted: 01 Apr 2013 05:13 PM PDT I was wondering if there is a way to find out the number of active locks held on a table in SQL Server? |
SSIS Expressions Date Format get just YY for year Posted: 01 Apr 2013 02:28 PM PDT I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried but it gives me the same result as I was thinking of a dirty/hack fix which would be something like but it just doesn't feel right.... what if someone runs my package 1000 years from now? lol. Any suggestions? I am trying to add a time stamp to a file name.. so today's file should be Auth040113.txt. I am creating the file name in a variable using expressions. |
Posted: 01 Apr 2013 02:34 PM PDT I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key: The results of running these stored procs has raised some questions about several objects on SQL Server that are being called repetitively. I can no longer remember what system is using them, and although the purpose is easily identified by looking at the source code for the given object, I'd like to identify what process is using them. Since I'm using SQL Server 2012, I thought I could use Extended Events to find the culprit. The following statement sets up an Extended Event. This Extended Event definition does not save it's results anywhere, so you must right-click the event and "Watch Live Data" to see events as they happen (or very near to as-they-happen). In the above code, I ran this extended event session, but it returns no events, even if I manually execute SQL text with the Since Extended Events are the "best new thing" in SQL Server for diagnostics, I must be missing something in my definition. |
Bitmask Flags with Lookup Tables Clarification Posted: 01 Apr 2013 06:21 PM PDT I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic. Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records. Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results. I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented. Is this as simple as creating a lookup table that has all possible search conditions? Example for 3 bits using (a & b) (Edit: Wrong bitwise op) The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows. Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated. Edit: A more concrete example using small data.
Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &). Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions. |
Slow Memcached: Average 10ms memcached `get` Posted: 01 Apr 2013 01:08 PM PDT We're using Newrelic to measure our Python/Django application performance. Newrelic is reporting that across our system "Memcached" is taking an average of Drilling down into the top dozen or so web views (by # of requests) I can see that some More details on the system architecture:
Isn't As far as I understand if you think "Memcache is too slow" then "you're doing it wrong". So am I doing it wrong? |
SSIS SQL Result to tab delimited txt file Posted: 01 Apr 2013 12:53 PM PDT I have created a package that Runs a Sql Query and then puts the results into a flat tab delimited txt file. When I opened the flat file in excel everything was in the right columns but in columns where i had a combination of alpha characters and numerical characters I was getting extra trailing blank spaces in certain records (as shown below). What I did was, added a derived Column step where i would do RTRIM(memberid) which successfully removed the trailing spaces but now the column was still aligned to the left while all the other records were aligned to the right. Has anyone ever come across this scenario and is there anything i can do so that the columns are all aligned to the RIGHT? |
Ubuntu / MySQL / Percona XtraBackup gives error "sh: 1: xtrabackup_55: not found" Posted: 01 Apr 2013 08:36 PM PDT Hello I installed Ubuntu 12.04.2 LTS on a fresh box. This installed MySQL 5.5.29. I installed percona-xtrabackup-2.0.6-521.tar.gz, and moved the executables to /usr/bin/ But innobackupex is giving an error: "sh: 1: xtrabackup_55: not found" The files are present and executable: Path is correct: I get the same results if I run Any ideas? |
Posted: 01 Apr 2013 12:53 PM PDT I would like to set But: i get many languages, but no one has the Question: Can i add another language based on an existing one, and set de field Can i modify an existing syslanguage ? All what i want, is te set it to 6 parmanently, because i need it to get the right week number. Thank you for help ! |
SQL Server 2012 catalog.executions to sysjobhistory - any way to join them? Posted: 01 Apr 2013 12:38 PM PDT I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job. |
UPDATE / SELECT incredibly slow in MySQL InnoDB Posted: 01 Apr 2013 01:13 PM PDT I have 1 table with 2.5 billion records(2.5 Cr.) and I am trying to get count of records based on indexed column. it takes 115 seconds to execute same. Query : |
How to suppress an informational raiserror message Posted: 01 Apr 2013 11:26 AM PDT I have a setup script that calls
with the variables filled in accordingly. Internally I can see sp_configure is calling Is there anything I can do to prevent those informational messages (which are useless to the script, I call |
BCNF when no keys are available Posted: 01 Apr 2013 11:08 AM PDT I have a problem understanding a particular case of BCNF. Let's suppose that we have a relation R(B, C, D) with df:{C->D}. Is my relation in BCNF and if yes why??? Is C considered as superkey? |
Using Oracle hierarchical queries for transposing field value into rows Posted: 01 Apr 2013 10:55 AM PDT One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm afraid it's not very efficient : I don't like that I have to use Thank you. |
Can I use replication/mirroring to have a "Current" database and a "Full History" copy? Posted: 01 Apr 2013 10:45 AM PDT I have a database that is used for current operations. The amount of data is starting to get a bit to much for the applications using it. Most of the data is older and is very rarely needed. I am wondering if there is a way to configure SQL Server to keep a mirror of the original database, but allow me to occasionally purge out old data (and not have it purge from the copy). NOTE: My database is currently SQL Server 2008 R2, but we will be upgrading to SQL Server 2012 soon. |
MySQL slave stuck in "Reading event from the relay log"? Posted: 01 Apr 2013 09:42 AM PDT
My problem is similar to this question. It looks like a bug, except for no one mentioned that verion 5.5.28 is effected. Here're the additional informations: mysql> show slave status\G mysql> show engine innodb status; mysqlbinlog --start-position=226591944 mysql-bin.006392 mysql> show keys from v3_cam_ip\G What I have done on one of two Slaves:
What should I do on the remaining slave? |
Real Time data collection - Help select Database Posted: 01 Apr 2013 09:01 AM PDT My Scenario: I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have to run network based queries on this data, the data does not have to be in the database for a long time, as I query I have to make calculations based on the current state of the sensors. I do not need historic data. There would be multiple queries made to the database. My requirements are; A real time database that is quick. Scalability is an issue , but can be overlooked as I exactly know how many sensors would send data. Large number of queries on the DB. This is an academic project so my preferences are to open source. I am not a database person , and the shear number of selections that there are is overwhelming. Any help and suggestions are really appreciated. |
How do I demonstrate Transaction Log activity? Posted: 01 Apr 2013 09:31 AM PDT Say I have the following setup: I'd like to convert each of the columns to varchar(max) like this: How can I demonstrate that the first command processes the whole table while the second command only processes metadata. I was thinking of using SET STATISTICS IO which reports thousands of logical reads for the first command and nothing for the other. I was also thinking of using DBCC LOG or fn_dblog. But I wasn't sure how to interpret or tie the results to the queries I issued. |
sql server partitioned view execution plan questions Posted: 01 Apr 2013 04:21 PM PDT I have created a partitioned view for 3 tables schema for table 2010 (same for all other tables) My query is My question is, the execution plan indicates that it is scanning all 3 tables. Instead of looking at the check constraint and saying table b has the information it needs. Why is that. i feel the execution plan should only show 1 table being scanned. Statistics turned on results Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tb_Sales2012'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tb_Sales2011'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tb_Sales2010'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. View Definition CREATE VIEW PartitionedTb_Sales AS SELECT * FROM tb_sales2010 UNION ALL SELECT * FROM tb_sales2011 UNION ALL SELECT * FROM tb_sales2012 |
BIT columns all "1" after a phpMyAdmin export/import Posted: 01 Apr 2013 02:51 PM PDT I have to import data from a MySQL database using phpMyAdmin because that's the only interface my hosting provider supports. I have exported my database from my local machine using phpMyAdmin. After that I imported the script file to my host. All of data in the columns that is BIT type are changed to '1'. Hosting database version is 5.5.29. Is it a phpMyAdmin problem, or MySQL version problem? How can I fix this? |
Moving SQL Server to different domain Posted: 01 Apr 2013 09:48 AM PDT What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain? What steps definitely/might need to be done for SQL Server before and after the new domain is joined? |
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837) Posted: 01 Apr 2013 06:00 PM PDT I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job; Which I guess it did, since I no longer can start the Oracle instance in question. This is the error I get when trying to connect as a non-DBA-user; When connecting as SYSDBA I get the following; ("Connected to an inactive instance." is my translation of a localized error message, might not be exactly correct) So, I'm in a catch 22 situation, I can't change the memory parameters without starting up, and I can't start up. Is there a way to change these parameters offline? |
Handling concurrent access to a key table without deadlocks in SQL Server Posted: 01 Apr 2013 02:20 PM PDT I have a table that is used by a legacy application as a substitute for Each row in the table stores the last used ID Occasionally the stored proc gets a deadlock - I believe I've built an appropriate error handler; however I'm interested to see if this methodology works as I think it does, or if I'm barking up the wrong tree here. I'm fairly certain there should be a way to access this table without any deadlocks at all. The database itself is configured with First, here is the table: And the nonclustered index on the Some sample data: The stored procedure used to update the values stored in the table, and return the next ID: Sample executions of the stored proc: EDIT: I've added a new index, since the existing index IX_tblIDs_Name is not being used by the SP; I assume the query processor is using the clustered index since it needs the value stored in LastID. Anyway, this index IS used by the actual execution plan: EDIT #2: I've taken the advice that @AaronBertrand gave and modified it slightly. The general idea here is to refine the statement to eliminate unnecessary locking, and overall to make the SP more efficient. The code below replaces the code above from Since our code never adds a record to this table with 0 in |
Posted: 01 Apr 2013 06:58 PM PDT My database contains 20,000 rows and the following query is slow. Please help me to speed this up: CREATE TABLE : My EXPLAIN : NOW I change query following : AND faster than before : 1.97s. So somebody help me for speedup more. |
How to modify an update in Oracle so it performs faster? Posted: 01 Apr 2013 09:58 AM PDT I have this query: The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function. What would you suggest? This is all the information that I believe it is relevant. This is the execution plan of the internal select: Table data: This is the script of the historical table: This is the other table: The temporary table is the result of FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE |
You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
Google Inc., 20 West Kinzie, Chicago IL USA 60610 |