Monday, August 19, 2013

[how to] Was it mandatory to put a "distinct" field as the first field in a query?

[how to] Was it mandatory to put a "distinct" field as the first field in a query?

Was it mandatory to put a "distinct" field as the first field in a query?

Posted: 19 Aug 2013 08:48 PM PDT

Just out of curiosity, looks like a distinct field must be placed ahead of any other fields, am I wrong?

See this example in SQLite,

sqlite> select ip, distinct code from parser; # syntax error?  Error: near "distinct": syntax error  sqlite> select distinct code, ip from parser; # works

Why is that? Do I really have a syntax error?

Counting the same column of different value sets in a single group by clause

Posted: 19 Aug 2013 08:42 PM PDT

I have a table (SQLite DB) like this,

CREATE TABLE parser (ip text, user text, code text);

Now I need to count how man code have a value of either 1, 2, or 3, and how many are not, group by ip field.

But as far as I can go, I can't do this altogether, but with two SQL phrases.

e.g 

select count(*) as cnt, ip   from parser   where code in (1, 2, 3)   group by ip   order by cnt DESC   limit 10

And a not in query.

So, can I merge the two queries into a single one?

Why isn't postgres prompting me about a new user?

Posted: 19 Aug 2013 08:37 PM PDT

I'm following these instructions to get postgres working with rails on Windows 7:

Run "Start Command Prompt with Ruby" then...

  1. createuser -U postgres -P NewUserName
  2. When it prompts for a password for new role, assign it a new password.
  3. No, the new role should not be a superuser.
  4. Yes, the new role should be allowed to create databases.
  5. No, the new role should not be allowed to create more new roles.
  6. Enter the postgresPWD you made when we installed PostgreSQL.

But this isn't happening. After I run #1, it asks me for a password, then skips straight to 5. This is completely screwing me over! Where did 2 through 4 go?

So then I tried doing it manually with createuser -s -U postgres -d -r (I have to do -U postgres because otherwise it asks for a password for a user that seems to be based on my Windows account, and no password I give it is successful...I tried fixing this by changing pg_hba.conf and then NOTHING worked)....so anyway I tried doing it manually and it was successful, but then I tried createdb newdb_name and it said createdb: could not connect to database template1: FATAL: password authentication failed for user "{my windows account}".

So I guess I could createdb as postgres, but then my rails commands to rake db:create would still fail. I need the active account to have full rights so that I can keep doing rails work.

I'm becoming really miserable about this. Please help...

Can listen_addresses really be set to a list?

Posted: 19 Aug 2013 08:19 PM PDT

I have a VM with IP address 192.168.0.192 running postgreSQL.

If I specify

listen_addresses = '*'

then I can connect from another VM at 192.168.0.191 and from localhost.

But I can't seem to use a list to tell postgreSQL to use those two addresses. If I change listen_addresses to a list:

listen_addresses = '192.168.0.191, localhost'

then I can no longer connect from 192.168.0.191.

I notice that almost all examples on stackexchange set listen_addresses to '*'. Is this because the list form does not work?

Show clients with staff assigned and no staff assigned

Posted: 19 Aug 2013 08:12 PM PDT

I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client  FROM clients cl, groups g, link_group lg  WHERE lg.zrud_group = ?   AND lg.zrud_group = g.zzud_group  AND cl.zzud_client = lg.zrud_client  ORDER BY cl.name_lastfirst_cs

It shows all clients in a group although the staff assignment is left out. If I add the staff in:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client, s.staff_name_cs AS Staff  FROM clients cl, groups g, link_group lg, staff s  WHERE lg.zrud_group = 'NEWHAN'  AND lg.zrud_group = g.zzud_group  AND cl.zzud_client = lg.zrud_client  ORDER BY cl.name_lastfirst_cs

It results in a blank result. Can anyone steer me to the correct way to build this?

ORA-12560 again

Posted: 19 Aug 2013 02:32 PM PDT

I have a Windows Server 2008 virtual machine with Oracle XE 11.2.0, the Oracle service is running and I can ping the machine from the Windows 7 64-bit host. The host's tnsnames.ora is correctly set up.

For several days I was able to connect to Oracle from the host machine.

Today, I'm again enlightened by the lovely error message ORA-12560 TNS protocol adapter error when I start sqlplus.exe.

Yes I have set ORACLE_SID and ORACLE_HOME environment variables.

Yes tnsnames.ora is correctly set up.

Yes the Oracle service is running.

Most forum entries on this error message suggest to use tnsping which should be in the bin folder of my Oracle client installation. However, a complete search for tnsping on the host machine didn't show any results.

Sigh. It was working for several days.

Any ideas?

Dividing DBCC CHECKDB over multiple days

Posted: 19 Aug 2013 02:32 PM PDT

I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days for very large databases, which basically consists of:

  • Dividing the tables in the database roughly equally between 7 buckets
  • Running a DBCC CHECKALLOC twice a week
  • Running a DBCC CHECKCATALOG once a week
  • Running a DBCC CHECKTABLE on one bucket each day of the week

Has anyone used this technique? Any existing scripts out there?

I'm concerned this may not actually cover everything that CHECKDB does; the Books Online documentation for CHECKDB says that in addition to CHECKALLOC, CHECKCATALOG and CHECKTABLE, it also:

  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. (SQL 2008 only)
  • Validates the Service Broker data in the database.

So here are my questions:

  1. Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)

  2. If so, are there ways to perform these additional checks separately?

  3. CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?

(Note: this will be a standard routine for thousands of existing databases across hundreds of servers, or at least every database over a certain size. This means that options like restructuring all databases to use CHECKFILEGROUP aren't really practical for us.)

Configuring PostgreSQL to match server configuration

Posted: 19 Aug 2013 02:17 PM PDT

We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram is free. PostgresSQL takes only 600Mb while webserver processes take 7Gb. Average CPU load is 25%.

Software is Ubuntu 12.04 and Postgres 9.1. Database size is 15Gb.

As load increased our application response time increased from 230ms to 450ms over last few months. Application takes 40% share while DB takes 60% of response time.

We cached a lot of things, but was wondering if we could get something by tweaking Postgres configuration. After bit of researched we found that default PostgreSQL configuration (https://gist.github.com/darkofabijan/9453c793ceec1ac6274d) is really conservative and that we should definitely tweak our configuration.

After running pgtune we got following recommended values.

maintenance_work_mem = 1GB   checkpoint_completion_target = 0.7   effective_cache_size = 44GB   work_mem = 288MB   wal_buffers = 4MB   checkpoint_segments = 8   shared_buffers = 15GB   max_connections = 200

Once we started running our PostgreSQL server with recommended values we got somehow regular spikes when application response time jumped to 2000ms+ while increase was in database response time. After running it for couple of hours we reverted to original Ubuntu 12.04/PostgreSQL 9.1 configuration.

Obviously we don't have much experience with running DBs. Both concrete recommendations regarding pgtune suggested values and pointers to good resources would be much appreciated.

Edit 1:

            name            |         current_setting          |        source  ----------------------------+----------------------------------+----------------------   application_name           | psql                             | client   client_encoding            | UTF8                             | client   DateStyle                  | ISO, MDY                         | configuration file   default_text_search_config | pg_catalog.english               | configuration file   external_pid_file          | /var/run/postgresql/9.1-main.pid | configuration file   lc_messages                | en_US.UTF-8                      | configuration file   lc_monetary                | en_US.UTF-8                      | configuration file   lc_numeric                 | en_US.UTF-8                      | configuration file   lc_time                    | en_US.UTF-8                      | configuration file   log_line_prefix            | %t                               | configuration file   log_timezone               | localtime                        | environment variable   max_connections            | 100                              | configuration file   max_stack_depth            | 2MB                              | environment variable   port                       | 5432                             | configuration file   shared_buffers             | 24MB                             | configuration file   ssl                        | on                               | configuration file   TimeZone                   | localtime                        | environment variable   unix_socket_directory      | /var/run/postgresql              | configuration file

Problems working with decode function when there is more then one expression

Posted: 19 Aug 2013 09:18 PM PDT

I am using Oracle 11g. I have these two tables:

Table acct: 

acctnbr || Name    || Mailing address     000001  || Slater  ||     000002  || Baker   || Alt    000003  || Parke   ||

Table addruse: 

acctnbr || Address    || Type    000001  || 1 aby rd   || Pri    000001  || 2 ctr strt || Res    000002  || 3 fbry rd  || Alt    000003  || 4 jnry rd  || Res

I am listing all the mailing addresses. So the query I need should compare the "mailing address" column from the acct table with the "type" column in the addruse table and list all those addresses. If the mailing address field is null, that means its using a primary address so it should grab the primary address from the addruse table but if there is no primary address then it should grab whatever there is in the addruse table. This is what I have so far: 

 select distinct a.acctnbr, a.Name,     DECODE(a.mailingaddress ,NULL,'PRI',a.mailingaddress) MAILCD,     b.address  from acct a, addruse b  where a.acctnbr = b.acctnbr  and   DECODE(a.mailingaddress,NULL,'PRI',a.mailingaddress) = b.type (+)

I dont know how to change this so that it should first check for a primary address and if there is no primary address, it should grab whatever there is like for acctnbr = 000003.

Index will be removed

Posted: 19 Aug 2013 12:50 PM PDT

I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning':

'MyTable' table  -Warning: The following schema-bound objects will be modified:    -View 'dbo.MyViewName': indexes and schema binding will be removed.

I remember from experience that if I actually go through with this, the binding will not be readded. In the past I've had to manually write a script to re-add the view. This doesn't seem right - why would it just remove it without re-adding it at the end of the operation?

It makes me think I don't have something set up correctly, or am going about this the wrong way - am I? Or is it 'correct' procedure to have to manually re-add things in this way after table changes are made?

How to troubleshoot db/app latency during transaction log backup

Posted: 19 Aug 2013 09:14 AM PDT

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups.

The application controls latency sensitive industrial machinery and we are finding that when the system has a high transaction volume occurring, the application has queries that run too long (>5 seconds for a brief period of time). These queries only run slow at the same time when the transaction log backups are happening so it appears to be related to the transaction log backups. When the system is performing without problems, our transaction log backups take 2-4 seconds and when we are under a heavier load and we have problems they are taking 6-7 seconds. That apparently is just enough to cause some FIFO message dispatch queues to fill up on the application.

First of all I was under the impression that transaction logs should be pretty transparent to the application, with no locking or anything else going on. Does this point to some kind of IO contention as being invoved if we are seeing database latency when we are doing the transaction log backup. What are the pros and cons of moving to something like a 5-minute transaction log backup cadence instead of 15-minute?

Disk backend is a NetApp FAS2220 with a bunch of 600 GB 10k SAS drives. The DBA is convinced that this is an application problem and not a database problem so I need to know how to troubleshoot this to either being a problem with the application or the database.

TLDR: Database or application latency seen under heavy load during transaction log backup. How to troubleshoot and resolve?

startup mount issues with Oracle

Posted: 19 Aug 2013 10:10 AM PDT

I am a newbie with Oracle so bear with me.

I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0). Logged in via sqlplus, I issue the command shutdown immediate;. I get the

Database closed.    Database dismounted.    ORACLE instance shut down

reponses. I exit out of sqlplus, and try to log back in (ORACLE_SID is set correctly) so I can issue the startup mount; command, but I get the 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

error. All of the Windows services involving my database dantest3 are started. Doing research, I found the oradim command, but while that starts my database and allowing me to login, that is the full startup, and I want to only be in "mount" mode.

I am logging in with the following command - 

sqlplus sys/[passwordforacct]@dantest3 as sysdba

If I try the simple sqlplus / as sydba, I get a 

ORA-12560: TNS:protocol adapter error

oradim command - 

oradim -start -sid dantest3

Requested variables -

TNS_ADMIN = C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN  ORACLE_SID = dantest3  ORACLE_HOME_11gR2 = C:\Oracle\product\11.2.0\dbhome_1

Any advice or suggestions would be greatly appreciated!

SQL Server 2008 - Question about index behaviour

Posted: 19 Aug 2013 01:31 PM PDT

I have a general question about advanced issues regarding index behaviour.

In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using the same name as before. Our tests seemed to run ok, but we later found out that our testing environments resolved to using the plans according to the old index definitions, while our production environment used the new ones. The tests had therefore produced the wrong results, and we changed these indexes back to the old versions in production, where the old filter definitions were immediately applied to the plans.

This worked fine for the past 6 months. Now however, we have the opposite problem. Our production environment has suddenly fallen back to using the plans for these 6 months old, falsely filtered indexes, where until a few weeks back it was still using the ones the existing indexes are supposed to use.

We've tested fixing this by again dropping these problem indexes, and this time creating them with a different name entirely. This seems to be working fine.

But my question is this: Considering that the indexes have been dropped (not just renamed), then created with the same name, and the query plan cache has been cleared AND the statistics updated several times, how is it possible that SQL Server seems to have a mind of its own and now has resolved to using ancient plans that I didn't even know could have existed any more?

Basically, how exactly does SQL Server store and use the data associated with indexes and their plans? How can you force SQL Server to clear that cache, wherever it may be, completely so that it can absolutely not simply decide to use ancient detrimental plans simply based on the same index name? How does all of this work, so that we can understand it and will never have to deal with this issue again?

Thanks!

EDIT:

It's now all but confirmed that these 6 months old filtered indexes were the reason. I restored the DB to a testing environment and ran problem queries against it, providing the wrong execution plans compared to another ancient testing environment. Checking each and every one of the indexes used by the older, functional environment, every single index definition was identical. I updated the stats, reorganized and rebuilt the indexes so none of them had fragmentation above 35, cleared the query plan cache, and still the problem persisted.

I then proceeded to find specifically those indexes involved in the query that were briefly filtered 6 months ago, dropped them and recreated them (the first time they still didn't work right, after the second attempt after another restore, they DID start working right). After this, I dropped and created the index with a different name that hasn't been used before, otherwise using the same definitions. This fixed it every time, and the execution planner would now use the correct indexes. Then I dropped these, created the indexes again using the filter definitions from 6 months back, again with a new name to ensure the query planner would use the new definitions instead of some ghost statistics from older ones. The failed plan produced after these indexes was identical to the ones initially used when all definitions fragmentation, statistics etc had been cleared and checked. Proving once and for all that despite all the seemingly available metadata, the execution planner was working under the assumption that the indexes were filtered and thus not usable, all along.

Do any of you know what could be going on? Or is this something that should be reported as a bug, regardless of how rare it might be. Because the implications of this behavior and the effects it's already had, are severe enough that currently I'm considering logging every index name just so none of them will ever be reused. Otherwise this paints a grim picture in that SQL Server may store ancient statistics in the background and start using them at any time, completely nullifying the structure and purpose of new indexes which may be completely business-critical. While it seems extremely likely that the failover had something to do with this, I still can't understand how perfectly working indexes suddenly be replaced by outdated and completely wrong definitions and statistics to the point where no manner of rebuilding them or updating the applicable metadata would help. With no real way to even diagnose that this has happened, other than a sudden decrease in performance, and quirky behaviour on the query planner's part.

"Use Database" command inside a stored procedure

Posted: 19 Aug 2013 02:40 PM PDT

I would like to dynamically use a database inside a stored procedure but the use database_name command is not supported inside procedures.

What alternatives are there?

How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

Posted: 19 Aug 2013 09:31 AM PDT

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:

add_index :users, [:username, :created_at]

That should utilize the index when I do a query like (I think):

User.where("username = ? and created_at > ?", 'jim', DateTime.now.yesterday)

But will it also utilize the index if I only use the username in a query?

User.where("username = ?", 'jim')

Run Multiple Scripts In One Transaction Across Multiple Servers [duplicate]

Posted: 19 Aug 2013 01:25 PM PDT

This question already has an answer here:

We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.

mongodb config servers not in sync

Posted: 19 Aug 2013 09:56 AM PDT

I have setup with 2 shards, with 2 replica servers and 3 config servers, and 2 mongos. I have following problems:

1) mongo config servers out of sync:

Aug 14 09:46:48 server mongos.27017[10143]: Sun Aug 11 09:46:48.987 [CheckConfigServers] ERROR: config servers not in sync! config servers mongocfg1.testing.com:27000 and mongocfg3.testing.com:27000 differ#012chunks: "d2c08c5f1ee6048e5f6fab30e37a70f0"#011chunks: "7e643e9402ba90567ddc9388c2abdb8a"#012databases: "6f35ec52b536eee608d5bc706a72ec1e"#011databases: "6f35ec52b536eee608d5bc706a72ec1e"

2) I use this document to sync servers: http://docs.mongodb.org/manual/tutorial/replace-config-server/ 3) After sync i restart one mongos server, and see this in logs:

Thu Aug 15 09:56:05.376 [mongosMain] MongoS version 2.4.4 starting: pid=1575 port=27111 64-bit host=web-inno.innologica.com (--help for usage)  Thu Aug 15 09:56:05.376 [mongosMain] git version: 4ec1fb96702c9d4c57b1e06dd34eb73a16e407d2  Thu Aug 15 09:56:05.376 [mongosMain] build info: Linux ip-10-2-29-40 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49  Thu Aug 15 09:56:05.376 [mongosMain] options: { configdb: "mongocfg1.testing.com:27000,mongocfg2.testing.com:27000,mongocfg3.testing.com:27000", keyFile: "/mongo_database/pass.key", port: 27111 }  Thu Aug 15 09:56:05.582 [mongosMain] SyncClusterConnection connecting to [mongocfg1.testing.com:27000]  Thu Aug 15 09:56:05.583 [mongosMain] SyncClusterConnection connecting to [mongocfg2.testing.com:27000]  Thu Aug 15 09:56:05.583 [mongosMain] SyncClusterConnection connecting to [mongocfg3.testing.com:27000]  Thu Aug 15 09:56:05.585 [mongosMain] SyncClusterConnection connecting to [mongocfg1.testing.com:27000]  Thu Aug 15 09:56:05.586 [mongosMain] SyncClusterConnection connecting to [mongocfg2.testing.com:27000]  Thu Aug 15 09:56:05.586 [mongosMain] SyncClusterConnection connecting to [mongocfg3.testing.com:27000]  Thu Aug 15 09:56:07.213 [Balancer] about to contact config servers and shards  Thu Aug 15 09:56:07.213 [websvr] admin web console waiting for connections on port 28111  Thu Aug 15 09:56:07.213 [Balancer] starting new replica set monitor for replica set replica01 with seed of mongo1.testing.com:27020,mongo2.testing.com:27020,mongo3.testing.com:27017  Thu Aug 15 09:56:07.214 [Balancer] successfully connected to seed mongo1.testing.com:27020 for replica set replica01  Thu Aug 15 09:56:07.214 [Balancer] changing hosts to { 0: "mongo1.testing.com:27020", 1: "mongo2.testing.com:27020" } from replica01/  Thu Aug 15 09:56:07.214 [Balancer] trying to add new host mongo1.testing.com:27020 to replica set replica01  Thu Aug 15 09:56:07.215 [Balancer] successfully connected to new host mongo1.testing.com:27020 in replica set replica01  Thu Aug 15 09:56:07.215 [Balancer] trying to add new host mongo2.testing.com:27020 to replica set replica01  Thu Aug 15 09:56:07.215 [Balancer] successfully connected to new host mongo2.testing.com:27020 in replica set replica01  Thu Aug 15 09:56:07.215 [mongosMain] waiting for connections on port 27111  Thu Aug 15 09:56:07.427 [Balancer] Primary for replica set replica01 changed to mongo1.testing.com:27020  Thu Aug 15 09:56:07.429 [Balancer] replica set monitor for replica set replica01 started, address is replica01/mongo1.testing.com:27020,mongo2.testing.com:27020  Thu Aug 15 09:56:07.429 [ReplicaSetMonitorWatcher] starting  Thu Aug 15 09:56:07.430 [Balancer] starting new replica set monitor for replica set replica02 with seed of mongo5.testing.com:27020,mongo6.testing.com:27020  Thu Aug 15 09:56:07.431 [Balancer] successfully connected to seed mongo5.testing.com:27020 for replica set replica02  Thu Aug 15 09:56:07.432 [Balancer] changing hosts to { 0: "mongo5.testing.com:27020", 1: "mongo6.testing.com:27020" } from replica02/  Thu Aug 15 09:56:07.432 [Balancer] trying to add new host mongo5.testing.com:27020 to replica set replica02  Thu Aug 15 09:56:07.432 [Balancer] successfully connected to new host mongo5.testing.com:27020 in replica set replica02  Thu Aug 15 09:56:07.432 [Balancer] trying to add new host mongo6.testing.com:27020 to replica set replica02  Thu Aug 15 09:56:07.433 [Balancer] successfully connected to new host mongo6.testing.com:27020 in replica set replica02  Thu Aug 15 09:56:07.712 [Balancer] Primary for replica set replica02 changed to mongo5.testing.com:27020  Thu Aug 15 09:56:07.714 [Balancer] replica set monitor for replica set replica02 started, address is replica02/mongo5.testing.com:27020,mongo6.testing.com:27020  Thu Aug 15 09:56:07.715 [Balancer] config servers and shards contacted successfully  Thu Aug 15 09:56:07.715 [Balancer] balancer id: web-inno.innologica.com:27111 started at Aug 15 09:56:07  Thu Aug 15 09:56:07.715 [Balancer] SyncClusterConnection connecting to [mongocfg1.testing.com:27000]  Thu Aug 15 09:56:07.716 [Balancer] SyncClusterConnection connecting to [mongocfg2.testing.com:27000]  Thu Aug 15 09:56:24.438 [mongosMain] connection accepted from 127.0.0.1:55303 #1 (1 connection now open)  Thu Aug 15 09:56:24.443 [conn1]  authenticate db: admin { authenticate: 1, nonce: "6cc9a76b79656179", user: "admin", key: "xxxxxxxxxxxxxxxxxxx" }  Thu Aug 15 09:56:26.676 [conn1] creating WriteBackListener for: mongo1.testing.com:27020 serverID: 520c7b87e4a4c3afa569b21a  Thu Aug 15 09:56:26.676 [conn1] creating WriteBackListener for: mongo2.testing.com:27020 serverID: 520c7b87e4a4c3afa569b21a  Thu Aug 15 09:56:26.678 [conn1] creating WriteBackListener for: mongo5.testing.com:27020 serverID: 520c7b87e4a4c3afa569b21a  Thu Aug 15 09:56:26.678 [conn1] creating WriteBackListener for: mongo6.testing.com:27020 serverID: 520c7b87e4a4c3afa569b21a  Thu Aug 15 09:56:26.679 [conn1] SyncClusterConnection connecting to [mongocfg1.testing.com:27000]  Thu Aug 15 09:56:26.679 [conn1] SyncClusterConnection connecting to [mongocfg2.testing.com:27000]  Thu Aug 15 09:56:26.680 [conn1] SyncClusterConnection connecting to [mongocfg3.testing.com:27000]  Thu Aug 15 09:57:33.704 [conn1] warning: inconsistent chunks found when reloading collection.documents, previous version was 8651|7||51b5c7a96b2903a0b3fac106, this should be rare  Thu Aug 15 09:57:33.714 [conn1] warning: ChunkManager loaded an invalid config for collection.documents, trying again  Thu Aug 15 09:57:34.065 [conn1] warning: inconsistent chunks found when reloading collection.documents, previous version was 8651|7||51b5c7a96b2903a0b3fac106, this should be rare  Thu Aug 15 09:57:34.076 [conn1] warning: ChunkManager loaded an invalid config for collection.documents, trying again  Thu Aug 15 09:57:34.491 [conn1] warning: inconsistent chunks found when reloading collection.documents, previous version was 8651|7||51b5c7a96b2903a0b3fac106, this should be rare  Thu Aug 15 09:57:34.503 [conn1] warning: ChunkManager loaded an invalid config for collection.documents, trying again  Thu Aug 15 09:57:34.533 [conn1] Assertion: 13282:Couldn't load a valid config for collection.documents after 3 attempts. Please try again.  0xa82161 0xa46e8b 0xa473cc 0x8b857e 0x93cb52 0x93f329 0x93ff18 0x94311f 0x9740e0 0x991865 0x669887 0xa6e8ce 0x7f4456361851 0x7f445570790d   /usr/bin/mongos(_ZN5mongo15printStackTraceERSo+0x21) [0xa82161]   /usr/bin/mongos(_ZN5mongo11msgassertedEiPKc+0x9b) [0xa46e8b]   /usr/bin/mongos() [0xa473cc]   /usr/bin/mongos(_ZN5mongo12ChunkManager18loadExistingRangesERKSs+0x24e) [0x8b857e]   /usr/bin/mongos(_ZN5mongo8DBConfig14CollectionInfo5shardEPNS_12ChunkManagerE+0x52) [0x93cb52]   /usr/bin/mongos(_ZN5mongo8DBConfig14CollectionInfoC1ERKNS_7BSONObjE+0x149) [0x93f329]   /usr/bin/mongos(_ZN5mongo8DBConfig5_loadEv+0xa48) [0x93ff18]   /usr/bin/mongos(_ZN5mongo8DBConfig4loadEv+0x1f) [0x94311f]   /usr/bin/mongos(_ZN5mongo4Grid11getDBConfigESsbRKSs+0x480) [0x9740e0]   /usr/bin/mongos(_ZN5mongo7Request5resetEv+0x1d5) [0x991865]   /usr/bin/mongos(_ZN5mongo21ShardedMessageHandler7processERNS_7MessageEPNS_21AbstractMessagingPortEPNS_9LastErrorE+0x67) [0x669887]   /usr/bin/mongos(_ZN5mongo17PortMessageServer17handleIncomingMsgEPv+0x42e) [0xa6e8ce]   /lib64/libpthread.so.0(+0x7851) [0x7f4456361851]   /lib64/libc.so.6(clone+0x6d) [0x7f445570790d]  Thu Aug 15 09:57:34.549 [conn1] scoped connection to mongocfg1.testing.com:27000,mongocfg2.testing.com:27000,mongocfg3.testing.com:27000 not being returned to the pool  Thu Aug 15 09:57:34.549 [conn1] warning: error loading initial database config information :: caused by :: Couldn't load a valid config for collection.documents after 3 attempts. Please try again.  Thu Aug 15 09:57:34.549 [conn1] AssertionException while processing op type : 2004 to : collection.system.namespaces :: caused by :: 13282 error loading initial database config information :: caused by :: Couldn't load a valid config for collection.documents after 3 attempts. Please try again.  Thu Aug 15 09:57:37.722 [Balancer] SyncClusterConnection connecting to [mongocfg1.testing.com:27000]  Thu Aug 15 09:57:37.723 [Balancer] SyncClusterConnection connecting to [mongocfg2.testing.com:27000]  Thu Aug 15 09:57:37.723 [Balancer] SyncClusterConnection connecting to [mongocfg3.testing.com:27000]

First mongos also have this error "warning: error loading initial database config information :: caused by :: Couldn't load a valid config for collection.documents after 3 attempts. Please try again."

but work for now.

Second mongos after restart don't work;

mongos> show collections  Thu Aug 15 09:57:34.550 JavaScript execution failed: error: {      "$err" : "error loading initial database config information :: caused by :: Couldn't load a valid config for collection.documents after 3 attempts. Please try again.",      "code" : 13282  } at src/mongo/shell/query.js:L128  mongos>

What is the next steps to recover config servers?

All advice are welcome.

MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups

Posted: 19 Aug 2013 02:56 PM PDT

I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error:

mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')      -> ;  ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

-

# df -h /tmp/  Filesystem            Size  Used Avail Use% Mounted on  tmpfs                 2.0G   12K  2.0G   1% /tmp  # df -i /tmp/  Filesystem            Inodes   IUsed   IFree IUse% Mounted on  tmpfs                2041621       7 2041614    1% /tmp  # mount | grep /tmp  tmpfs on /tmp type tmpfs (rw,size=2048M)

Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.

EDIT:

Server_01

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"  1876765

but this also happened on server with much less tables:

Server_02

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"  49514

I was using this query to list all databases but as it didn't work with tmpfs I've just replaced it with simpler one (SHOW DATABASES...).

I was watching disk space on /tmp mounted with tmpfs and there still was a plenty of space so I'm not sure how it could run out of space?

Basically I've got a problem with logical backups on the server with ~8000 DBs - it takes many hours (~24) to complete this task. I've created a simple BASH script (please see below) and instead of mysqldump I'm using mydumper as initial tests showed that it's much faster.

Backups running very fast initially and then slowing down dramatically:

# ./backup.sh  Backing up database number: 1  Completed in: 0.016  Backing up database number: 2  Completed in: 0.078  Backing up database number: 3  Completed in: 0.074  Backing up database number: 4  Completed in: 0.068  Backing up database number: 5  Completed in: 0.071  Backing up database number: 6  Completed in: 0.060  Backing up database number: 7  Completed in: 0.067  Backing up database number: 8  Completed in: 0.070  Backing up database number: 9  Completed in: 0.065  .....  Backing up database number: 107  Completed in: 10.749  Backing up database number: 108  Completed in: 12.125  Backing up database number: 109  Completed in: 11.313  Backing up database number: 110  Completed in: 11.572  Backing up database number: 111  Completed in: 11.371  .....

Script:

#!/usr/bin/env bash    DATA_DIR="/tmp/mysqlbackup"  LOCKFILE=/tmp/backup.lock  NOW=$(date +%Y%m%d)  COUNT=1    if [ -f "$LOCKFILE" ]; then          echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"          exit 0    else            echo $$ > $LOCKFILE            if [ ! -d $DATA_DIR/$NOW ]; then                  mkdir -m 700 -p $DATA_DIR/$NOW          fi            while read DB; do                    (( COUNT++ ))                    echo "Backing up database number: $COUNT"                    START=$(date +%s.%N)                    mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"                    ELAPSED=$(date +%s.%N)                      printf "Completed in: %.3F\n" $(echo $ELAPSED - $START | bc)            done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"            echo "Removing backup dir...";            rm -rf $DATA_DIR/$NOW            rm -f $LOCKFILE            exit 0    fi

Why does my SQL Server show more than half a million active tasks?

Posted: 19 Aug 2013 08:04 PM PDT

select task_state,count(*)  from sys.dm_os_tasks  group by task_state

I ran the above statement on a SQL Server instance, and found it had about 633,000 records.

task_state       -------------- -----------  RUNNABLE       2  RUNNING        32  DONE           633115  SUSPENDED      99

How can I Close/Kill the useless tasks?

The MDW data collector have about 4000 page allocate in tempdb per time.

And this cause the IO pressure when server in busy time.

This is production server, We do not want to restart the service. And the version number is 11.0.3000.

The Max Worker Count is :1216  CPU Count:48  Hyperthread ratio:12  MaxDop: 8

Scheduler_Id is 0 - 47 and the amount of rows are average. and other columns are null.

How to get data comparations from two mysql tables [on hold]

Posted: 19 Aug 2013 11:01 AM PDT

What I have: The next structure:

Table ChrM

  • LID (PRIMARY with auto increment)
  • LOCUS (varchar (9)) Example value: (At1g30500) (Fixed Number of LOCUS values =173 rows)
  • StartPoint (INT) (Exclusive value assigned by locus)

  • EndPoint (INT) (Exclusive value assigned by locus)

    Actual table looks like the following 
        | LID |   LOCUS      | StartPoint | EndPoint|         1    AT1G30500        734         273         2    AT3G15690        11415       8848         3    AT3G05690        262900      195300         .        .              .          .        173   At5g01410        366700      264418

Table DMRs

  • idDMRs (Primary with auto increment)
  • StartPos (INT)(Exclusive value assigned by idDMRs)
  • EndPos (INT) (Exclusive value assigned by idDMRs)
  • Other...
Actual table looks like this 
        | idDMRs |   StartPos  | EndPos | OTHER 1| OTHER 2 | OTHER n             1         1003        716         .       .         .             .           .          .          .       .         .             156       197126      195371      .       .         .             172       197299      196119      .       .         .             201       197233      195621      .       .         .             .           .           .         .       .         .                20633     262857      262857      .       .         .

What I want : Select all values from table DMRs that fulfill the next condition,

EndPos >= EndPoint AND StartPos <= StartPoint

This for every LOCUS in table ChrM (Total of 173 times) to get an (LOCUS, idDMRs, EndPos, StartPos, OTHER1, OTHer2, OTHER n) array.

As a newbie to Mysql I tried doing this one by one printing Table ChrM in paper and inserting LOCUS respective StartPoint and EndPoint by hand and querying individually LOCUS by LOCUS in the following manner:

SELECT * FROM DMRs    WHERE EndPos >= 195300 AND StartPos <= 262900;

And the results are what I want, but I wonder if I will ever finish to analyze all my data when I have 50 different DMRs tables to analyze doing this 173 times for table.

Example of my query output 
         |  idDMRs  | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n               156      197126    195371      .       .         .               172      197299    195419      .       .         .               201      197233    195621      .       .         .
This is the result I want to get for every LOCUS 
        | LOCUS |   idDMRs  | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n           AT3G05690    156      197126    195371      .       .         .           AT3G05690    172      197299    195419      .       .         .           AT3G05690    201      197233    195621      .       .         .

So, in conclusion I would like help on how to resolve this problem or any advice on what I should read or know in order to resolve this on my own.

Moving large databases

Posted: 19 Aug 2013 09:09 PM PDT

I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space).

Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need to know the safest way of copying the databases over to a new server.

All advice appreciated!

Dropping Hypothetical Indexes

Posted: 19 Aug 2013 12:09 PM PDT

In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.

I have a database that is full of DTA remnants that I would like to cleanup; however, when I try to drop the object I always receive an error telling me that I cannot drop the object "because it does not exist or you do not have permission". I am a full sysadmin on the server so would expect to have rights to do anything.

I've tried this with both DROP STATS and DROP INDEX statements but both give me the same error.

Has anyone deleted these before and is there a trick I'm missing?

Addendum

Poking around in this, I just noticed that if I R-Click on the object, both the 'Script As' and 'DELETE' options are greyed out.

SA permissions issues with many nested objects

Posted: 19 Aug 2013 06:09 PM PDT

I have a broker application that's relatively complicated.

Today, after I made some changes, I started getting the error:

The server principal 'sa' is not able to access the database 'XYZ' under the current security context.

The whole scenario up to the point of the error is:

(In Database ABC)

  • A broker message gets submitted into a queue
  • The queue reader picks it up
  • The queue reader updates a table to indicate the work is starting
  • This table has an update trigger on it. The trigger
    • Checks a table in database XYZ to make sure the inserted value for this field is valid
    • The table is accessed via a synonym

The check in the trigger I believe is what is causing the issue.

If I run the update manually, it works fine. I have also used EXECUTE AS to run the update manually as sa which works fine.

Other relevant facts:

  • sa is the owner of both database ABC and database XYZ
  • there's no funny business with the sa account - it's db_owner role in both DBs as well

Is there some sort of strange scoping happening because all this is running in the context of broker?

Updates

Some more info:

  • DB ownership chaining is on at the server level but not in the DBs. I turned it on and it made no difference.
  • Using a three part name instead of a synonym didn't make a difference
  • Profiler trace shows that the statements are running as SA and not another login
  • Setting both databases TRUSTWORTHY to ON didn't make a difference
  • If I run the queue activation proc manually, it processes correctly (under my credentials).

Database user specified as a definer

Posted: 19 Aug 2013 11:09 AM PDT

I have a view in my database. problem is below

Error SQL query: 

SELECT *   FROM `lumiin_crm_prod`.`v_contact`   LIMIT 1 ;

MySQL said:

1449 - The user specified as a definer ('lumicrm'@'%') does not exist

i Google for a solution

User is created for Host & not for Global.

How to create the User for Global ????

How do I execute an Oracle SQL script without sqlplus hanging on me?

Posted: 19 Aug 2013 04:09 PM PDT

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.:

  • If the script contains any named substitution variable that has to be entered by the user, sqlplus should return with an error instead of prompting for the variable -- I cannot use set define off, as some of these scripts need to accept command line parameters that have to be resolved with &1

  • The script must not "hang" when it doesn't contain an exit; at the end.

    Solved: I think now that I can achieve this by wrapping the sql-script in a secondary "caller script". I.e., the caller script calls the other script with @ and then has a fixed exit; after that. The other script doesn't need an exit that way.

  • Anything else: If it would require a prompt, it should return with an error.

How can i do this with Oracle (and sqlplus or something else)?

SSRS appears to be ignoring Permissions set using Report Manager

Posted: 19 Aug 2013 07:09 PM PDT

I have setup SSRS on SQL Server 2008 in native mode.

As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports.

I have also created a local user on the machine, I went into Report Manager as Admin, and at the top level set permissions that should assign the local user to all roles.

When I login to the machine as that user, and then navigate to Report Manager I just get the heading for the page, but do not see any of the folders that are configured.

I've checked and the folders are set to inherit parent permissions and they are showing the newly created local user in there too.

It seems odd that I have set the permissions, yet SSRS is still not showing what I should be able to see. Is there another step I need to take other than configuring the permissions in Report Manager?

When logged in as the newly created local user:

Report Manager - Shows the heading for the page, but no folders/items    Web Service URL (http://machine/ReportServer) - rsAccessDeniedError

How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

Posted: 19 Aug 2013 09:07 PM PDT

I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server. I don't want to copy everything, only old rows, so that the table on the main server stays small-ish. So I have to SELECT the data I want and only move (and delete) that.

Keep in mind that there is a lot of data, and I don't want to copy it all with a mysqldump if I don't need to, especially if it's going to lock the table while it's being copied.

The best I've come up with is a PHP script, which I will post as a tentative answer, although I'm certain it's not the best option.

Couldn't install SQL Server 2012 on machine with Windows 7 SP1, VS 2010 SP1

Posted: 19 Aug 2013 06:55 PM PDT

I am trying to install SQL Server 2012 RTM on my pc, I have installed Windows 7 SP1, VS 2010 SP1 but it again and again giving this error:

A network error occurred while attempting to read from the file:
D:\Microsoft SQL Server 2012 RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi
Error help link:
http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=sqlncli.msi%40PublishProduct%401316

When I go to the Microsoft link I find

We're sorry
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.

How can resolve this, at first i thought adding current user in Display Replay controller making the problem, then uninstalled everything and again tried installing but couldn't succeed. Can anyone suggest what may be causing these problem and possible resolve for it. I have also SQL Server 2008 R2 installed which I want to keep simultaneously with 2012

Additional Information's:

Its on local drive i.e on my harddisk, so there should be no error and Its the RTM version of SQL 2012. I find below logs on C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Feature: SQL Client Connectivity SDK Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Native Client Access Component Component error code: 1316 Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20121022_164723\sqlncli_Cpu64_1.log Error description: A network error occurred while attempting to read from the file: D:\Microsoft SQL Server 2012 RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi Error help link: go.microsoft.com/…

This problem not yet solved, although other people installed SQL2012 with this installer on their PC's. I think something wrong with my PC or some settings i am using. Please can anyone help me out?

SQL Query Formatter

Posted: 19 Aug 2013 01:40 PM PDT

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 (Ubuntu) based solution.

I have seen a online version but nothing as a installable.

Eclipse Based IDE's are a plus as well

Example:

select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc

to something like

SELECT f1, f2, fname, lName  FROM tblName  WHERE f1 = true  AND fname IS NOT NULL  ORDER BY lName ASC

Here is a online example:

But I would rather this be in a local environment

Related:

UPDATE:

Looking at this:
Posted by at No comments:

[SQL Server] Partition Function SQL Server?

[SQL Server] Partition Function SQL Server?

Partition Function SQL Server?

Posted: 06 Jun 2013 04:24 AM PDT

Hi all experts,My question is my do we need Partition function?What would have happen if microsoft would have not included Partition Function feature in SQL Server?

Where to start

Posted: 22 Jul 2013 02:30 AM PDT

Hello,I'm hoping someone might be able to point me in a direction, even if its pointing me to a different forum.What i'm looking for is how to get started with learning how to set up a data warehouse. We are looking at maybe purchasing a report system but first we would need to cleanse and structure our database.I know this is kind of a big pond and i'm just looking for a place to start. I'm not huge on books so if there is a class i should look at taking or website,forums, whatever works.Thanks in advance.

Urgent - DB connection through maintenance job is not working

Posted: 19 Aug 2013 01:57 AM PDT

What am I missing hereExec maintenance plan fails for stored procedure, saying it could not find the object.also the automatically created job is failing(no surprise )BUTIf I create a job executing the same prodcedure directly on sql server agent , it works fine the sp is very simple it has only "select 1" , what I am trying to say is , no system file access inside the sp.my windows account or the user that sql agent is running on are are member of the sysadmin role.thanks

How to read a variable from a excel sheet to find data in my database?

Posted: 18 Aug 2013 10:40 PM PDT

Hi,I have a Excel sheet with usernames, I would like to loop through the usernames and see what info exists in the database, based on each username. Where do I start? Regards
Posted by at No comments:

[MS SQL Server] Transaction not getting cleared in SIMPLE recovery model ?

[MS SQL Server] Transaction not getting cleared in SIMPLE recovery model ?

Transaction not getting cleared in SIMPLE recovery model ?

Posted: 19 Aug 2013 04:12 AM PDT

Hi,I tried to repro a scenario for log-clearing the log in SIMPLE recovery model. I know if there is an ACTIVE TXN, and if we issue a CHECKPOINT in SIMPLE recovery model, nothing happens i.e. 0 VLFs gets cleared in my below scenario. But i commited my explicit transaction, still log_reuse_wait_Desc is showing ACTIVE TRANSACTION and log is not getting cleared. I repro'd the same scenario by inserting records using a WHILE-Loop and it works as expected but when I use GO <n-times> to insert records, the txn is still open. Can anyone figure-out why log is not getting cleared or do i missing something over here ???My SQL Server details=============Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Below is my repro steps. Pl don't go with the output values as I have done multiple times but no use. Log doesn't get cleared. Just follow my steps/cmds and you should be able to reproduce the same.USE [master]GOCREATE DATABASE [db1] ON PRIMARY( NAME = N'db1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB ) LOG ON( NAME = N'db1_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1_log.LDF',SIZE = 3MB , ---4 VLFs since size < 64mbFILEGROWTH = 1MB)GOALTER DATABASE db1 SET RECOVERY SIMPLE;GODBCC LOGINFO(db1)GO/*FileId FileSize StartOffset FSeqNo Status Parity CreateLSN2 720896 8192 22 2 64 02 720896 729088 0 0 0 02 720896 1449984 0 0 0 02 974848 2170880 0 0 0 0*/USE db1GOCREATE TABLE t1(C1 INT IDENTITY,C2 CHAR (8000) DEFAULT 'a' --- char datatype takes all the size even 1 char is inserted. use DATALENGTH() Function);GO-- i opened a new session/window/spid in the ssms and started the below explicit transactionBEGIN TRAN INSERT INTO t1 DEFAULT VALUES GO 600-- I came back to original window where i created the databasedbcc loginfo(db1);select recovery_model_desc,log_reuse_wait_descfrom sys.databaseswhere name = 'db1' ;/*recovery_model_desc log_reuse_wait_descSIMPLE ACTIVE_TRANSACTION */dbcc opentran(db1);/*Transaction information for database 'db1'.Oldest active transaction: SPID (server process ID): 57 UID (user ID) : -1 Name : user_transaction LSN : (22:63:2) Start time : Aug 19 2013 9:54:32:103PM SID : 0x0105000000000005150000007a6fe3176386abd260a96a96e8030000DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/-- I confirmed from log there is 1 Active txn Select [Current LSN],[Operation], [Num Transactions] from fn_dblog(null,null) where Operation in ('LOP_XACT_CKPT','LOP_XACT_CKPT','LOP_END_CKPT') /*Current LSN Operation Num Transactions 00000016:0000002e:0001 LOP_XACT_CKPT 1 00000016:0000002e:0002 LOP_END_CKPT NULL */-- i went to new window and committed the explcit transaction COMMIT; -- Came back to old windowCHECKPOINT; --to clear the logdbcc loginfo(db1);-- All are active (Status=2). none of the VLFs have been made inactiveselect recovery_model_desc,log_reuse_wait_descfrom sys.databaseswhere name = 'db1';/*SIMPLE ACTIVE_TRANSACTION*/dbcc opentran(db1);/*Transaction information for database 'db1'.Oldest active transaction: SPID (server process ID): 57 UID (user ID) : -1 Name : user_transaction LSN : (22:63:2) Start time : Aug 19 2013 9:54:32:103PM*/checkpoint;goSelect [Current LSN],[Operation], [Num Transactions] from fn_dblog(null,null) where Operation in ('LOP_XACT_CKPT','LOP_XACT_CKPT','LOP_END_CKPT')go/*Current LSN Operation Num Transactions00000022:000001af:0001 LOP_XACT_CKPT 100000022:000001af:0002 LOP_END_CKPT NULL00000022:000001b1:0001 LOP_XACT_CKPT 100000022:000001b1:0002 LOP_END_CKPT NULL*/-- Though i committed the Explicit open txn, why is it still Active and not allowing VLF's to clear.Can anyone pl let me know what is that I am missing here?Thanks in advance.

SAN Migration - DataFiles movement to new drives

Posted: 18 Aug 2013 03:56 PM PDT

Hi,We have got new space (SAN) allocated recently by storage folks to one of our database server to resolve I/O issues with some specific drives (D: & E:). Inorder to make use of the new drives, i am trying to move all the files (user DB + SYSTEM DB DataFiles) that are present on the current drives (bad drives) to the new one's (drives in good condition). Before making the technical plan, I just want to make sure I am not missing anything. I am putting all the steps that I am going to include in the plan, Please help me out if I am missing anything.[b]Environment:1) Windows 2003 server.2) Microsoft SQL Server 2008 (SP3) - 10.0.5768.0 (X64) Nov 4 2011 11:32:40 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)3) Clustered (2 Node)[/b]4) Drive D: has Data files & E: has TEMPDB files.Steps:-====================================================1) Bring down all the services on the database server.2) Enable the new drives that are assigned, and assume we are naming them as [b]X: (Old D:) & Y (Old E:)[/b].3) Start copying all the files to the new drives from the respective drives.4) Once Copy is successful, try renaming the old drives to [b]D: -> I: and E: -> J:[/b].5) Once you rename the old drives, start renaming the new drives [b]X: -> D: and Y: -> E:[/b].6) Once renaming is done, try starting all the services on the database.7) Make sure all the DB's are up and is able to accept requests/connections.8) Does it require any Maintenance tasks (ShrinkFiles, UpdateStats etc) post moving all required files? Please advice?I am aware there is one more option (Attach/Dettach) to achieve this, but since we are moving system datafiles, it is ruledout.Please advice if there are any other ways (easy) of doing it. Thanks,Nagarjun.

Benefits of DBCC Check DB and performance

Posted: 19 Aug 2013 12:16 AM PDT

I have some confusion here with DBCC , as i know that DBCC will take a snapshot of the DB to perform a Check DB. Am i correct? If yes, then is there any benefit in using a snapshot in DBCC and how will DBCC perform a check DB on system DBs... Can any one please clarify me....Thanks in advance !!!!
Posted by at No comments:

[Articles] Opinions and Votes

[Articles] Opinions and Votes

Opinions and Votes

Posted: 18 Aug 2013 11:00 PM PDT

When we look to improve software, we are often expressing our opinion on which items need to be handled first. It's helpful to keep in mind that it's an opinion being expressed, and not a vote, no matter how much it is solicited by a company.
Posted by at No comments:

[SQL 2012] SSRS 3.0 Parameters input issue

[SQL 2012] SSRS 3.0 Parameters input issue

SSRS 3.0 Parameters input issue

Posted: 18 Aug 2013 09:32 PM PDT

Hi all,I am building a report that will make a comparison with figures for the last 3 months, and last 12 months. For this I will build 3 datasets with the exact same structure, with the difference that I need each one of them the data will be different.The way I am trying to do it is to go to a specific dataset and on the parameters tab choose the parameter and do as example below:=Parameters!Period.Value-3 The parameter above would be for the dataset that needs to show the figures for the last quarter, the period field is an integer and every month as a number attached, so I figured that if for this specific dataset in the parameters tab there is an instruction for the parameter in the expression it should work... It doesn't, anyone has a suggestion for this?Best regards,Daniel

async_network_io high on reading data from SQL 2012 instance

Posted: 19 Aug 2013 02:08 AM PDT

Hi,after hearing some colleagues from development complain about "slow database" I started some investigations and noticed constantly report a high level of network_io waits over TCP/IP. Server Configuration: [SQL2012TEST]2x 6-core CPUs, HT enabled, 96GB of Ram, 10GbE (MTU 1500), X-cable 1 GbE (MTU 9000), RAID1, RAID5, JBOD and SSD drive configurations.SQL2012 SP1 11.0.3368Data Source Table: [dbo].[TestData] (bigint, datetime2(7), bigint, tinyint, float, int, int)Table: 106'847'023 rows @ ~42bytes/row, 7 columns, total bytes: 4'705'978'766[u]Initial test:[/u]running bcp from my workstation (1Gbe Network)bcp "dbo.TestData" out nul -n -T -SSQL2012TEST -dTESTDATA [b]Total Duration:[/b] 330sec[b]Avg MBytes/Rows per sec:[/b] 12.9 / 324'000Repeating the same test with a simple SqlDataReader implementation shows even worse results.[b]Total Duration:[/b] 398sec[b]Avg MBytes/Rows per sec:[/b] 10.7 / 267'000I can rule out disk issues as BCP is writing to NUL and SqlDataReader writes to BinaryWriter.Null. I can rule out network issues (so far), NtTTcp reports consistent throughput of 905MBit with almost zero errors/retransmits.The XE sessions (sqlos.waitinfo) tell me that there is only one wait-type: (ASYNC_)NETWORK_IO. Accumulated total wait time for the SPID running bcp[b]bcp:[/b] 160 sec[b]SqlDataReader:[/b] 220 secWell, I am wondering, what is the issue here? What is causing the high wait time, deserialization of the TDS stream?

Questions: SOS_SCHEDULER_YIELD, CPU Affinity, Core Licensing

Posted: 18 Aug 2013 08:32 PM PDT

Hello there, some of this post might be a bit rambling, but bear with me. If anyone wants further information let me know and I can dig it up.One of our clients servers is showing a reasonable amount of SOS_SCHEDULER_YIELD waits. It's the highest wait type present in the system at around 22% of total waits. They are clearly visible when I run an intensive query and take a delta of the waits stats. I've seen 17 seconds of SOS_SCHEDULER_YIELD waits reported for a 15 second period, correct me if I'm wrong but this basically equates to at least an entire core being idle for the entire operation. We also see a small amount of ASYNC_NETWORK_IO, but this is expected with the particular revision of the software.I'm trying to isolate why these waits are occurring, as it appears to be CPU contention slowing them down. There is plenty of headroom in the IO subsystem and we have about double the buffer pool that we realistically need. The vast majority of reads appear to be logcial, not physical. We don't see continually high CPU usage, it averages around 10-20% across the system in what is a 8 vCPU VMWare system. However, we've seen similar systems where increasing the amount of cores on the VM provides a noticeable performance benefit. The problem I have here is that most of our clients run with 4 cores, and we then suggest an upgrade to 8 cores if they are experiencing performance issues. We simply shouldn't be seeing CPU contention on this system.A DBA from one of our sites has emailed us the following advice:"Just another quick thought –with VM's we have found setting the processor affinity in MSSQL engine properties is a vital config for VM's and MSSQL. This means that MSSQL VM's will exclusively use the same processor for each thread rather than potentially bouncing it around like a yo yo across the available CPU resources that VMware manages at a much lower level, it can result in all sorts of odd cpu bottleneck issues and load issues like high cpu context switching, interrupt queues etc and so forth which are detrimental across the whole VM node."Now I've read up on this and it seems like a very bad idea to be considering, however, in the context of SQL Server running on VMWare, does that statement have any weight to it?For some background: At the back end the host is a dual 6 Core System with hyperthreading enabled. The SQL Server has 8 cores assigned and 2 other servers have 4 cores each assigned (total of 16), one of these servers (apart from the SQL server) might be applying reasonable pressure on the host. I'm inclined to suggest that we subscribe the hosts at 1:1 to eliminate competition for CPU resources on the host.I also have a query about core licensing. If a server is only licensed for 4 cores, but has 8 vCPU's allocated does SQL apply licensing restraints and only utilise 4 cores? Or do all of them get used. On the system in question I'm seeing 8 active schedulers, which seems to indicate the latter. However, i'm not 100% positive they've gone for Core licensing as opposed to Server+CAL.

Reusable component in SSIS/SSRS/SSAS

Posted: 18 Aug 2013 11:29 PM PDT

Hi Team,I want to know, do we have any re-usable components(like Macros,etc) for SSIS/SSRS/SSAS? If yes, please let me know. I'm intersted to know its functionality. Thanks.CheersJim

How do I use Analysis Services?

Posted: 18 Aug 2013 06:10 AM PDT

Hi,I have an Operational Datastore (ODS) instance consisting of several relational databases that are populated daily from exports from the OLTP databases. I use SSIS 2012 for this.The databases in the ODS are replicates of the databases in the OLTP.I also hold web analytics etc in the ODS and I will be combining data from all these sources for MI reporting and also for data analysis and visualization through a BI dashboard.There won't be much data to start off with, but I want to build the system for future use when there will be more data.Do I build a datawarehouse from the ODS databases, or do I build datamarts, or do I create logical views on top of the ODS databases that can be used by MI reporting and BI tools?I think performance at this point in time will be met by simple logical views. Is this wise or do I invest my time in designing a proper datawarehouse from the ODS now?Also, should I import data from the ODS into the datawarehouse using Analysis Services? Is this the correct use of it?Thanks for any help offered.
Posted by at No comments:

[T-SQL] Query Help - To delete old files

[T-SQL] Query Help - To delete old files

Query Help - To delete old files

Posted: 07 Aug 2013 11:27 PM PDT

Hi All,Looking for assistance to develop T-SQL codeRequirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldfThanks in advance for any help.

How to restrict insertion depends on condition?

Posted: 18 Aug 2013 05:16 PM PDT

Hi Friends,create table travel(user_id varchar(10),from varchar(10),to varchar(10),Lodging varchar(10),foodBill varchar(10),DailyAllowance varchar(10))insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore','4500','200',' ')insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore',' ',' ','500')now mY requirement is when the user select Lodging & FoodBill Daily allowance ll be null.if users choose DailyAllowance that Lodging & FoodBill ll be Null.how to write procedure for these condition?

SQL:StatementCompleted trace event?

Posted: 18 Aug 2013 06:14 PM PDT

Hi,I have found one of the SQL blogs for tracing SP completed duration as below codeIn this code, How do we include SQL:StatementCompleted trace event?[code="sql"] CREATE PROCEDURE [StartTrace] AS BEGIN DECLARE @trc INT DECLARE @TraceID INT DECLARE @maxfilesize BIGINTSET @maxfilesize = 100DECLARE @file NVARCHAR(500) SELECT @file = N'D:\TraceCollection\Test_Trace'EXEC @trc = SP_TRACE_CREATE @traceid = @TraceID output, @options = 2, --TRACE_FILE_ROLLOVER @tracefile = @file, @maxfilesize = @maxfilesize, @stoptime = NULL, @filecount = 10000-- Set the events --Event number 43 is SP:Complete --right after 43, we have number like 1, 12, 14 etc. which are column numbers of event DECLARE @on BIT, @ret INT SET @on = 1 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 1, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 13, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 14, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 15, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 18, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 28, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 34, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 35, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 48, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 59, @onEXEC SP_TRACE_SETFILTER @TraceID, 28, -- 28 is a column number for "Object Type" 0, -- and 0, -- == 8272 --we are filtering that only SPs should come and store in trace file-- Set the trace status to start EXEC sp_trace_setstatus @TraceID, 1END GO[/code]Thanks

Automated SQL code to XML test data files using systems Tables

Posted: 18 Aug 2013 08:34 AM PDT

I created a stored procedure which produces xml test data from any table using [INFORMATION_SCHEMA.COLUMNS] The stored proc is supposed to create 3 xml tests files - Customers, Booking, and Ancillary. Where a customer record number ends in a 1-10 integer value, create that number of Childrenselect Customer 1 then there will be 2 bookings and each of those bookings will have 2 ancillaries.E.g. Customer 2 will have 2 bookings and each of those bookings will have 2 ancillaries.E.g. Customer 3 will have 3 bookings and each of those bookings will have 3 ancillaries Customer seems to be working perfectly but I cannot seem to make the logic work with the child relationship with Booking and Ancillary plus the SQL code does not work.Once the sql script is created it should be able generate test xml files for any table in the system.Any insight would be helpful in how to fix the booking and ancillary problem, Thank you.Create table[code="sql"]/****** Object: Table [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] Script Date: 08/18/2013 22:09:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS]( [TABLE_CATALOG] [nvarchar](128) NULL, [TABLE_SCHEMA] [nvarchar](128) NULL, [TABLE_NAME] [sysname] NOT NULL, [COLUMN_NAME] [sysname] NULL, [ORDINAL_POSITION] [int] NULL, [COLUMN_DEFAULT] [nvarchar](4000) NULL, [IS_NULLABLE] [varchar](3) NULL, [DATA_TYPE] [nvarchar](128) NULL, [CHARACTER_MAXIMUM_LENGTH] [int] NULL, [CHARACTER_OCTET_LENGTH] [int] NULL, [NUMERIC_PRECISION] [tinyint] NULL, [NUMERIC_PRECISION_RADIX] [smallint] NULL, [NUMERIC_SCALE] [int] NULL, [DATETIME_PRECISION] [smallint] NULL, [CHARACTER_SET_CATALOG] [sysname] NULL, [CHARACTER_SET_SCHEMA] [sysname] NULL, [CHARACTER_SET_NAME] [sysname] NULL, [COLLATION_CATALOG] [sysname] NULL, [COLLATION_SCHEMA] [sysname] NULL, [COLLATION_NAME] [sysname] NULL, [DOMAIN_CATALOG] [sysname] NULL, [DOMAIN_SCHEMA] [sysname] NULL, [DOMAIN_NAME] [sysname] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressOptin', 2, NULL, N'YES', N'bit', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine1', 3, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine2', 4, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'CustomerID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AddressID', 2, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'EmailAddress', 3, NULL, N'YES', N'nvarchar', 255, 510, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AncillaryBrandCode', 4, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccommodationTypeCode', 1, NULL, N'YES', N'nvarchar', 20, 40, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccomodationArrivalDate', 2, NULL, N'YES', N'datetime', NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressCountry', 3, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressID', 4, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)[/code]Based on the Master XML I created the following SQLto XML test datat script:[code="sql"]---SQL Customer --it seems worksCreate procedure [dbo].[HealthCreateTestEntities](@CustomersTocreate int)asbegindeclare @CustomerCounter int, @ChildElements int, @Bookings int, @Ancillary intdeclare @XMLFileCustomers table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))select @CustomerCounter = 1--Customersinsert @XMLFileCustomers values('Customer','<?xml version="1.0"?>')insert @XMLFileCustomers values('Customer','<CustomerContacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="CustomerContactSummary.xsd">')while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end select @Bookings = @ChildElements, @Ancillary = @ChildElements--Customerinsert @XMLFileCustomers values('Customer','<CustomerContact>') insert @XMLFileCustomers select 'Customer','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@CustomerCounter as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@CustomerCounter as varchar) when COLUMN_NAME = 'CustomerDOB' or COLUMN_NAME like '%date' then cast(dateadd(month, @CustomerCounter, cast('01-12-2012' as date)) as nvarchar) when COLUMN_NAME like '%Time' then '20:59:15' when COLUMN_NAME = 'LandlinePhoneSource' then cast(@CustomerCounter as varchar) when data_Type = 'bit' then case when @CustomerCounter % 2 = 0 then '1' else '0' end ELSE COLUMN_NAME + cast(@CustomerCounter as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'CustomerContact' set @CustomerCounter = @CustomerCounter + 1 ENDinsert @XMLFileCustomers values('Customer','</ShortBookingSummaries>')insert @XMLFileCustomers values('Customer','</CustomerContact>')insert @XMLFileCustomers values('Customer','</CustomerContacts>') select * from @XMLFileCustomerswhere XMLRecordType = 'Customer'end[/code][code="sql"]--BOOKING does not work declare @XMLFileBookings table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))select @CustomerCounter = 1while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end select @Bookings = @ChildElements, @Ancillary = @ChildElements while @Bookings > 0 begin insert @XMLFileBookings values('BookingStartTag','<Booking>') insert @XMLFileBookings values('Booking','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Bookings as varchar)+'</BookingRef><TotalRevenue>'+cast(@Bookings * 100 as varchar)+'</TotalRevenue>') Insert @XMLFileBookings select 'Booking','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@Bookings as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@Bookings as varchar) when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0' then 'Booking' +''+ cast(@Bookings as varchar) else COLUMN_NAME + cast(@Bookings as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'BookingSummary' set @CustomerCounter = @CustomerCounter + 1end select @Bookings = @Bookings - 1 select @Ancillary = @ChildElementsselect * from @XMLFileBookingswhere XMLRecordType = 'Booking'end[/code][code="sql"]--ANCILLARY also does not workselect @CustomerCounter = 1declare @XMLFileAncillaries table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end while @Ancillary > 0 begin insert @XMLFileAncillaries values('AncillaryStartTag','<Ancillary>') insert @XMLFileAncillaries values('Ancillary','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Ancillary as varchar)+'</BookingRef><AncillaryRevenue>'+cast(@Ancillary * 100 as varchar)+'</AncillaryRevenue>') Insert @XMLFileAncillaries select 'Ancillary','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@Ancillary as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@Ancillary as varchar) when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0' then 'Ancillary' +''+ cast(@Ancillary as varchar) else COLUMN_NAME + cast(@Ancillary as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'AncillarySummary' insert @XMLFileAncillaries values('Ancillary','</Ancillary>') select @Ancillary = @Ancillary - 1 end select @Bookings = @Bookings - 1 select @Ancillary = @ChildElements end set @CustomerCounter = @CustomerCounter + 1endselect * from @XMLFileAncillarieswhere XMLRecordType = 'Ancillary'---the 1 is the number of customers selected, it could be any number.exec [dbo].[HealthCreateTestEntities] 1[/code]

how to view log files

Posted: 18 Aug 2013 03:11 AM PDT

This gives me the location of the log file. How do I view it?SELECT name, physical_name AS current_file_locationFROM sys.master_files where name = 'MyDatabase'
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog