[how to] SQL Server nvarchar(n) where n affects performance

---

• SQL Server nvarchar(n) where n affects performance
• Restoring old MySQL database settings
• MySQL MyISAM index causes query to match no rows; indexes disabled, rows match
• Sql Server Replication Transactional Default value in [msrepl_tran_version]
• Percona Cluster Failed with Haproxy : Connection Limit Exceeded
• How to transpose/convert rows as columns in mysql
• Complex constraint across all data in a table
• Oracle Text - Changing SYNC to MANUAL not working always
• Does the mysql parameter "max_connections" apply for unix socket connections?
• Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters
• Restoring of subscriber database failed
• SqlPackage does not pick up variables from profile
• Speeding up mysqldump / reload
• How to disable SSMS table designer timeout?
• Database Mail sending functionality not working on local system
• createdb: could not connect to database postgres: FATAL: could not write init file
• Repeated values in group_concat
• MySQL PDO Cannot assign requested address
• DB2 critical alert "database partition availability"
• How I prevent deadlock occurrence in my application?
• How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB
• Named Pipe Provider Error code 40
• How to removing or reduce the size of redo logs in MySQL cluster
• What methods are available to create a snapshot of an oracle database for testing purposes?
• SQL Server making arithmetic abort enabled
• Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?
• Can IMPLICIT_TRANSACTIONS set on database level?
• WAL Database log on separate disk good idea RAID10?

SQL Server nvarchar(n) where n affects performance Posted: 10 Aug 2013 04:14 PM PDT I have 3 tables with identical data. First table has the column defined as nvarchar(max). Second table has the column defined as nvarchar(4000). Third table has the column defined as nvarchar(800). This column is an included column on an index (on all 3 tables). If I run the same query on all three tables, here are the statistics: Table 'table1'. Scan count 91676, logical reads 368637, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 2605 ms, elapsed time = 3030 ms. Table 'table2'. Scan count 91676, logical reads 368668, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 1669 ms, elapsed time = 1897 ms. Table 'table3'. Scan count 91676, logical reads 276220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 1061 ms, elapsed time = 1306 ms. First why does the logical reads decrease with the lower max size setting? Second why does changing from max to the lower value even speed up anything? Based on the lob reads being 0, I don't think any of the data is in lob-storage.

Restoring old MySQL database settings Posted: 10 Aug 2013 07:55 PM PDT I know this question may be similar to some others posted but think it may be slightly different, so hopefully it's ok. Basically I just reinstalled Windows as I bought a new HD and I am in the process of copying all the MySQL databases back to the new installation dir; however I'm not sure if it's ok to copy all the default MySQL databases back with it? Those being: cdcol mysql performance_schema phpmyadmin test webauth I assume one of these contain details I want to keep such as info about the users and also other stuff like mysql variables - unless that is found in a MySQL .ini file or something? I'm not 100% sure but I'm pretty sure I'm still using the same version of MySQL. If it matters I am using XAMPP. So basically my question is can I copy the old folders above over the new ones and what are these folders/databases used for exactly (other than the obvious phpmyadmin one)?

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match Posted: 10 Aug 2013 08:25 AM PDT I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found. Commands ran: CREATE TABLE mytable (id1 int, id2 int, score float) ENGING MyISAM; LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score); ALTER TABLE mytable ADD INDEX id1_index (id1); SELECT COUNT(*) FROM mytable; # returns 50 billion SELECT COUNT(DISTINCT id1) FROM mytable; # returns 50K, should be about 50M SELECT COUNT(*) FROM mytable WHERE id1 = 49302; # returns 0 results ALTER TABLE mytable DISABLE KEYS; SELECT * FROM mytable WHERE id1 = 49302 LIMIT 1; # returns 1 row Is this a bug with MySQL, or does this behavior make sense for some reason? Note: When I ran ALTER TABLE mytable ENABLE KEYS; just now, the command is acting like it is building an index for the first time (it's still running after 30 minutes, and memory usage is at 80 GB, which matches my setting of myisam_sort_buffer_size=80G. I'll reply when this command finishes running (the original ALTER .. ADD INDEX.. took 7.5 hours, so it may be a bit). Update: Running SHOW PROCESSLIST indicates "Repair with keycache" is taking place with my ENABLE KEYS command.

Sql Server Replication Transactional Default value in [msrepl_tran_version] Posted: 10 Aug 2013 07:14 AM PDT Type Replication : Transactional publication with updatable subscriptions I am using EntityFramework 5 and he is generating the table (DATABASEFIRST) whit this collum, unnecessary, so every insert whit this tools creating a default min value. id_User id_Album date location msrepl_tran_version 1 2222 2013-06-14 NULL 9AB6B9A6-4BB4-416C-AFEB-5851410F545F 1 22427 2013-08-10 NULL 00000000-0000-0000-0000-000000000000 1 22428 2013-08-10 NULL 00000000-0000-0000-0000-000000000000 At moment the replication is working fine, but how many problems can I get on futures maintenances if keep this value on system? Anyway, I know how create a Backup whitout replication Info and import this to EF5 and change ips to main sql.

Percona Cluster Failed with Haproxy : Connection Limit Exceeded Posted: 10 Aug 2013 04:23 AM PDT We have 3 node PXC cluster setup on amazon ec2. Its been working fine for around 4-5 months. Since last week, every node starting to fail due to MySQL connection limit exceeded error. we increased the connection limit and still no help. We had about 1-2hr downtime since last week. Setup as follows. App1 ---> Haproxy1 ---> PXC Node1 App2 ---> Haproxy2 ---> PXC Node2 App3 ---> Haproxy3 ---> PXC Node3 Each haproxy configured with 1 active mysql node and 2 backup servers server SQL1 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup server SQL2 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 server SQL3 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup We also use clustercheck script from percona to monitor mysql nodes on port 50000 with help of xinex.d Any suggestions?

How to transpose/convert rows as columns in mysql Posted: 10 Aug 2013 04:15 AM PDT I have table structures like below Subjects Table +----+------+------+--------+---------+ | id | C_Id | G_Id | status | subject | +----+------+------+--------+---------+ | 1 | 13 | 4 | 1 | Telugu | | 2 | 13 | 4 | 1 | Hindi | | 3 | 13 | 4 | 1 | English | | 4 | 13 | 4 | 1 | Maths | | 5 | 13 | 4 | 1 | Physics | | 6 | 13 | 4 | 1 | Biology | | 7 | 13 | 4 | 1 | Social | +----+------+------+--------+---------+ +----+-----+-----+--------+--------+-------+-------+-------+ | id | Cid | Gid | Examid | rollno | subId | Marks | paper | +----+-----+-----+--------+--------+-------+-------+-------+ | 1 | 13 | 4 | 1 | 1 | 1 | 14.50 | 1 | | 2 | 13 | 4 | 1 | 2 | 1 | 12.00 | 1 | | 3 | 13 | 4 | 1 | 1 | 2 | 13.00 | 1 | | 4 | 13 | 4 | 1 | 2 | 2 | 15.00 | 1 | | 5 | 13 | 4 | 1 | 1 | 3 | 16.00 | 1 | | 6 | 13 | 4 | 1 | 2 | 3 | 18.00 | 1 | | 7 | 13 | 4 | 1 | 1 | 4 | 19.00 | 1 | | 8 | 13 | 4 | 1 | 2 | 4 | 23.00 | 1 | | 9 | 13 | 4 | 1 | 1 | 5 | 21.00 | 1 | | 10 | 13 | 4 | 1 | 2 | 5 | 24.00 | 1 | | 11 | 13 | 4 | 1 | 1 | 6 | 20.00 | 1 | | 12 | 13 | 4 | 1 | 2 | 6 | 19.00 | 1 | | 13 | 13 | 4 | 1 | 1 | 7 | 20.00 | 1 | | 14 | 13 | 4 | 1 | 2 | 7 | 21.00 | 1 | | 15 | 13 | 4 | 2 | 1 | 1 | 45.00 | 2 | | 16 | 13 | 4 | 2 | 2 | 1 | 40.00 | 2 | | 17 | 13 | 4 | 2 | 1 | 1 | 32.00 | 3 | | 18 | 13 | 4 | 2 | 2 | 1 | 33.00 | 3 | | 19 | 13 | 4 | 2 | 1 | 2 | 80.00 | 1 | | 20 | 13 | 4 | 2 | 2 | 2 | 89.00 | 1 | | 21 | 13 | 4 | 2 | 1 | 3 | 39.00 | 2 | | 22 | 13 | 4 | 2 | 2 | 3 | 38.00 | 2 | | 23 | 13 | 4 | 2 | 1 | 3 | 41.00 | 3 | | 24 | 13 | 4 | 2 | 2 | 3 | 45.00 | 3 | | 25 | 13 | 4 | 2 | 1 | 4 | 34.00 | 2 | | 26 | 13 | 4 | 2 | 2 | 4 | 38.00 | 2 | | 27 | 13 | 4 | 2 | 1 | 4 | 32.00 | 3 | | 28 | 13 | 4 | 2 | 2 | 4 | 33.00 | 3 | | 29 | 13 | 4 | 2 | 1 | 5 | 31.00 | 1 | | 30 | 13 | 4 | 2 | 2 | 5 | 34.00 | 1 | | 31 | 13 | 4 | 2 | 1 | 6 | 33.00 | 1 | | 32 | 13 | 4 | 2 | 2 | 6 | 31.00 | 1 | | 33 | 13 | 4 | 2 | 1 | 7 | 35.00 | 2 | | 34 | 13 | 4 | 2 | 2 | 7 | 31.00 | 2 | | 35 | 13 | 4 | 2 | 1 | 7 | 43.00 | 3 | | 36 | 13 | 4 | 2 | 2 | 7 | 38.00 | 3 | +----+-----+-----+--------+--------+-------+-------+-------+ So Far I have wrote the code select ta.rollno, ta.StdNm, max(case when s.subject = 'Telugu' AND tm.paper=1 then tm.Marks end) Telugu, max(case when s.subject = 'Telugu' AND tm.paper=2 then tm.Marks end) Telugu1, max(case when s.subject = 'Telugu' AND tm.paper=3 then tm.Marks end) Telugu2, max(case when s.subject = 'Hindi' AND tm.paper=1 then tm.Marks end) Hindi, max(case when s.subject = 'Hindi' AND tm.paper=2 then tm.Marks end) Hindi1, max(case when s.subject = 'Hindi' AND tm.paper=3 then tm.Marks end) Hindi2, max(case when s.subject = 'English' AND tm.paper=1 then tm.Marks end) English, max(case when s.subject = 'English' AND tm.paper=2 then tm.Marks end) English1, max(case when s.subject = 'English' AND tm.paper=3 then tm.Marks end) English2, max(case when s.subject = 'Maths' AND tm.paper=1 then tm.Marks end) Maths, max(case when s.subject = 'Maths' AND tm.paper=2 then tm.Marks end) Maths1, max(case when s.subject = 'Maths' AND tm.paper=3 then tm.Marks end) Maths2, max(case when s.subject = 'Physics' AND tm.paper=1 then tm.Marks end) Physics, max(case when s.subject = 'Physics' AND tm.paper=2 then tm.Marks end) Physics1, max(case when s.subject = 'Physics' AND tm.paper=3 then tm.Marks end) Physics2, max(case when s.subject = 'Biology' AND tm.paper=1 then tm.Marks end) Biology, max(case when s.subject = 'Biology' AND tm.paper=2 then tm.Marks end) Biology1, max(case when s.subject = 'Biology' AND tm.paper=3 then tm.Marks end) Biology2, max(case when s.subject = 'Social' AND tm.paper=1 then tm.Marks end) Social, max(case when s.subject = 'Social' AND tm.paper=2 then tm.Marks end) Social1, max(case when s.subject = 'Social' AND tm.paper=3 then tm.Marks end) Social2 FROM tbl_cmarks tm INNER JOIN tbl_classes tc ON tm.Cid = tc.C_Id INNER JOIN tbl_admission ta ON ta.rollno = tm.rollno INNER JOIN tbl_subjects s on tm.subId = s.id where tm.Cid = 13 and tm.Examid=2 group by ta.rollno Here in my script i am passing the subjects Names as hard Coded But in future we don't know how many subjects will have in Class 13 where should i change in my query to pass the subject Ids instead of Subject Names and that too not as hard coded. Can i take all subject Ids for Particular Class into a variable and pass in the Max(Case) statement.? Is it possible? Please review my code and give me a clue/help, to do work this code. Thank you in advance.

Complex constraint across all data in a table Posted: 10 Aug 2013 01:25 PM PDT We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or one: select count(id) from jobs where status in ('P', 'G'); We're using explicit transactions so ideally this check would happen on commit and abort the transaction if the invariant doesn't hold. Handling any racy processing that throws the occasional error is much easier for us to deal with than suddenly ending up with more than one job that is 'in process'. The solution only needs to work with Postgres so we're quite happy to take a non-standard solution to this. We're currently on 8.4, but we'll be upgrading at some point to 9.x if that makes any difference.

Oracle Text - Changing SYNC to MANUAL not working always Posted: 10 Aug 2013 06:21 AM PDT Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below query is run using CallableStatement for this purpose {call CTX_DDL.REPLACE_INDEX_METADATA('IXFTS_ABC_TABLE','REPLACE METADATA SYNC (MANUAL)')} In some environments this works fine but in production environments the SYNC option is still "ON COMMIT". No error is thrown when this is not updated. The index is not a partitioned index. Any pointers to troubleshoot further will be helpful. [Update] I could reproduce this in my local machine rarely. Looks like running the same sql 2 or 3 times is working. Not sure why this fails the first time.

Does the mysql parameter "max_connections" apply for unix socket connections? Posted: 10 Aug 2013 10:46 AM PDT The documentation does not specify if max_connections apply only to TCP connections. The way it is said, it looks like it does apply for any kind of connection, but I'd like to be sure because otherwise, I could use it when the "Too many connection" problem arises, even if the (last) SUPER connection slot is already in use.

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters Posted: 10 Aug 2013 04:00 AM PDT I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey. I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date: Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]), Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY]))) This works well until you throw Requisition Status into the mix. I have rows for each requisition for every day in 2013. For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date. When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved. Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table. The Requisition Status table looks like this: Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job. Sometimes it is 1, sometimes is is more than 1. It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.

Restoring of subscriber database failed Posted: 10 Aug 2013 07:58 AM PDT We have replicated a database from live to test in SQL Server 2005. Now we need to restore the subscriber database which is in test to the same server without replication setting in the restored database. How can we achieve it?

SqlPackage does not pick up variables from profile Posted: 10 Aug 2013 05:58 PM PDT I want to upgrade a database using .dacpac and sqlpackage.exe here is how I run sqlpackage: SqlPackage.exe /Action:Publish /SourceFile:"my.dacpac" /Profile:"myprofile.publish.xml" The error I get is: * The following SqlCmd variables are not defined in the target scripts: foo. I have verified that myprofile.publish.xml file does contain that var: <ItemGroup> <SqlCmdVariable Include="foo"> <Value>bc\local</Value> </SqlCmdVariable> I also verified that project that creates dacpac does publish successfully from within visual studio using myprofile.publish.xml What else could I be missing? (I'm using SQL Server 2012)

Speeding up mysqldump / reload Posted: 10 Aug 2013 09:58 AM PDT Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the load. I am planning to "SET GLOBAL innodb_max_dirty_pages_pct=0;" at some point before the dump. I am curious to know which combination of settings will get me the fastest dump and reload times? SCHEMA stats: 26 myisam tables 413 innodb ~240GB of data [--opt= --disable-keys; --extended-insert; --quick, etc] --no-autocommit ?? vs prepending session vars like: "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" Are the mysqldump options equivalent or not really? Thanks for your advice!

How to disable SSMS table designer timeout? Posted: 10 Aug 2013 05:37 AM PDT How do i disable the Table Designer timeout in SQL Server Management Studio? Background This KB Article seems to indicate that if you stop overriding the Designer timeout: To resolve this behavior, use one of the following methods: Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio. it is supposed to revert to the Execution timeout: But rather than timing out never it times out after 30 seconds: i've also tried setting the table designer to the maximum value of 65,535. Unfortunately that just causes the timeout to happen after 18 hours, rather than 30 seconds. How do i disable the table designer timeout in SQL Server Management Studio? SQL Server Management Studio: 9.00.1399.00 Pre-emptive snarky comment: What kind of table change are you making that needs more than 18 hours to complete? i'm changing a column in a 130 GB table from text to nvarchar(max).

Database Mail sending functionality not working on local system Posted: 10 Aug 2013 12:58 PM PDT I am using Database Mail functionality to send mail from a SQL Server 2008 database via following stored procedure execution: EXEC sp_send_dbmail @profile_name = 'MyProfile', @recipients = 'abc@companyname.com', @subject = 'Test message', @body = 'Congrats Database Mail Received By you Successfully.' I have tried with my gmail account profile on my local system it's working properly but not with my company or outlook profile. Error message: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 . Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond abc.j.i.ooo:pp). ) Reference http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx What would be the problem? Thanks

createdb: could not connect to database postgres: FATAL: could not write init file Posted: 10 Aug 2013 07:58 PM PDT RedHat Enterprise Server 3.0 32 Bits psql (PostgreSQL) 8.2.3 user: postgres server is running: /soft/postgres/8.2.3/bin/pg_ctl start pg_ctl: another server may be running; trying to start server anyway 2013-05-09 11:23:07 BRST---:FATAL: lock file "postmaster.pid" already exists 2013-05-09 11:23:07 BRST---:HINT: Is another postmaster (PID 12810) running in data directory "/opt/psql/dba/bdadms/data1/pstg"? pg_ctl: could not start server Examine the log output. I had just created a new database cluster with initdb; but when I run createdb: 8.2.3:postgres:pstg:>/soft/postgres/8.2.3/bin/createdb pstg createdb: could not connect to database postgres: FATAL: could not write init file 8.2.3:postgres:pstg:>/soft/postgres/8.2.3/bin/createdb postgres createdb: could not connect to database template1: FATAL: could not write init file 8.2.3:postgres:pstg:>/soft/postgres/8.2.3/bin/createdb template1 createdb: could not connect to database postgres: FATAL: could not write init file any clues as to the cause and possible solutions to this problem?

Repeated values in group_concat Posted: 10 Aug 2013 03:58 PM PDT I have two tables, first the table food and Second is Activity: INSERT INTO food (`id`, `foodName`) VALUES (1, 'food1'), (2, 'food2'), (3, 'food3'), (4, 'food4'), (5, 'food5'), (6, 'food6'), ; CREATE TABLE Activity (`id` int,`place` varchar(14),`food_id` int,`timing` TIME,`date_and_time` DATETIME) ; INSERT INTO Activity (`id`,`place`, `food_id`,`timing`,`date_and_time`) VALUES (1, 'place1', 1, '10:30am','2013-05-01'), (2, 'place1', 1, '12:30pm','2013-05-01'), (3, 'place1', 1, '04:30pm','2013-05-01'), (4, 'place2', 2, '10:30am','2013-05-02'), (5, 'place2', 2, '12:30pm','2013-05-02'), (6, 'place2', 2, '4:30pm','2013-05-02'), (7, 'place1', 2, '10:30am','2013-05-02'), (8, 'place1', 2, '12:30pm','2013-05-02'), (9, 'place1', 2, '4:30pm','2013-05-02'), (10, 'place2', 3, '10:30am','2013-05-03'), (11, 'place2', 3, '12:30pm','2013-05-03'), (12, 'place2', 3, '4:30pm','2013-05-03') ; For now I'm using the following query: SELECT a.activity_type AS Activity, COUNT(DISTINCT p.id) AS Products, CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ', ') USING utf8) AS Categories FROM food AS p JOIN ( SELECT activity_type , prod_id FROM activity WHERE activity_type <> '' GROUP BY activity_type , prod_id ) AS a ON p.id = a.prod_id GROUP BY activity_type ORDER BY Products DESC ; Could you please help me, I need output in the below format: place | food_id | Timings | | | |---------------------|Date | | |Time1 |Time2 | Time3 | | ---------------+----------+------------------------------------------| place1 | 1 | 10:30am| 12:30pm| 4:30pm |2013-05-01(MAX timestamp)| place2 | 1 | 10:30am| 12:30am| 4:30am |2013-05-01(MAX timestamp)|

MySQL PDO Cannot assign requested address Posted: 10 Aug 2013 01:58 PM PDT Can someone help me with this error? [08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning: PDO::__construct(): [2002] Cannot assign requested address (trying to connect via tcp://****:3306) in /var/www/*** [08-Apr-2013 17:44:08 Europe/Berlin] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Cannot assign requested address' in /var/www/*** I have a Server with a lot connections per second; out of about 100 Connections, a single one got this error. I've tried this recommendation from stackoverflow however it does not solve my problem.

DB2 critical alert "database partition availability" Posted: 10 Aug 2013 12:58 AM PDT I have a existing DB2 database that I'm just getting started with and I have connected IBM Data Studio (web) to it and it says that it has the alert "Database Partition Availability" but it doesn't give a real description (the description field says "The availability of the database partitions"). Does this mean that the database partition is offline? I can't find anything related on google.

How I prevent deadlock occurrence in my application? Posted: 10 Aug 2013 05:58 AM PDT I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database. All the tables in the database have innodb engine. I also created indexes on each tables. Now I am doing load testing using Jmeter version 2.9 locally for 200 users concurrently. During the load testing, in a specific page action I got Deadlock Found error. I changed my original query to the new one but again same error is occurring. I have written save_interactions function which takes four parameters interaction array,module_id,course_id,user_id & is been called so many times by the AJAX script. The following script inserts the record if the specific interaction_id is not present in that table otherwise the update query will get fire. public function save_interactions($interaction_array,$modid,$cid,$uid) { foreach($interaction_array as $key=>$interact_value) { $select_query = $this->db->query("SELECT COUNT(*) AS total FROM `scorm_interactions` WHERE `mod_id`='".$modid."' AND `course_id`='".$cid."' AND `user_id`='".$uid."' AND `interaction_id`='".$interact_value[0]."'"); $fetchRow = $select_query->row_array(); if($fetchRow['total']==1) { $update_data = array( "interaction_type"=>$interact_value[1], "time"=>$interact_value[2], "weighting"=>$interact_value[3], "correct_response"=>$interact_value[4], "learner_response"=>$interact_value[5], "result"=>$interact_value[6], "latency"=>$interact_value[7], "objectives"=>$interact_value[8], "description"=>$interact_value[9] ); $this->db->where('mod_id', $modid); $this->db->where('course_id', $cid); $this->db->where('user_id', $uid); $this->db->where('interaction_id', $interact_value[0]); $this->db->update('scorm_interactions', $update_data); }else { $insert_data = array( "user_id"=>$uid, "course_id"=>$cid, "mod_id"=>$modid, "interaction_id"=>$interact_value[0], "interaction_type"=>$interact_value[1], "time"=>$interact_value[2], "weighting"=>$interact_value[3], "correct_response"=>$interact_value[4], "learner_response"=>$interact_value[5], "result"=>$interact_value[6], "latency"=>$interact_value[7], "objectives"=>$interact_value[8], "description"=>$interact_value[9] ); $this->db->insert('scorm_interactions', $insert_data); } } } I got this type of error: Deadlock found when trying to get lock; try restarting transaction UPDATE `scorm_interactions` SET `interaction_type` = 'choice', `time` = '10:45:31', `weighting` = '1', `correct_response` = 'Knees*', `learner_response` = 'Knees*', `result` = 'correct', `latency` = '0000:00:02.11', `objectives` = 'Question2_1', `description` = '' WHERE `mod_id` = '4' AND `course_id` = '5' AND `user_id` = '185' AND `interaction_id` = 'Question2_1' ; Filename: application/models/user/scorm1_2_model.php Line Number: 234 Can anyone please suggest me how to avoid Deadlock?

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB Posted: 10 Aug 2013 08:58 AM PDT I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told MySQL Cluster scales much better. How much data does it take to show a performance benefit to MySQL Cluster vs. an InnoDB schema? Or, is there a better way to demonstrate MySQL Cluster's merits? EDIT Perhaps an important note: My cluster is currently a heterogeneous cluster with 4 machines. On each machine, I have given an equal amount of Data and Index Memory; 4GB, 2GB, 2GB, and 1GB respectively. The machines are running i7's and are connected over a Gigabit Lan. NumOfReplicas is set to 2. EDIT This application is a low-usage analytics database, which has roughly 3 tables >= 200M rows and 5 tables <= 10K rows. When we use it, it takes 15 seconds to run our aggregate functions. My boss asked me to research MySQL Cluster, to see if we could increase performance, since we thought aggregate functions could run pretty well in parallel.

Named Pipe Provider Error code 40 Posted: 10 Aug 2013 08:58 PM PDT I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP and Named Pipes) with SQL Server Management Studio. But sqlcmd throws an error: Login timeout expired Help!

How to removing or reduce the size of redo logs in MySQL cluster Posted: 10 Aug 2013 04:58 AM PDT Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we disable Redo logging in MySQL cluster.

What methods are available to create a snapshot of an oracle database for testing purposes? Posted: 10 Aug 2013 10:47 AM PDT I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done. I'm a bit confused as to what my options are. I've looked into creating a standby. It seems like that may be overkill for what we need and the pages describing how to create one are confusing to one with as little experience as I. Questions Is a standby the best fit or is import/export a viable option? Are there other methods?. Thanks!

SQL Server making arithmetic abort enabled Posted: 10 Aug 2013 10:48 AM PDT I am using SQL Server 2008. In my stored procedures, I often have to call for XML data type, functions (like split). Is it possible to permanently enable arithmentic abort for a database? I know it can be done using properties window > options but is there any script i can use to turn it on? The reason to look for script is obvious; i have too many DBs to do it manually. I assume set arithabort off and set arithabort on turns on arithabort only for the context.

Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases? Posted: 10 Aug 2013 10:45 AM PDT Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when creating user databases.

Can IMPLICIT_TRANSACTIONS set on database level? Posted: 10 Aug 2013 10:45 AM PDT When I have to investigate problems caused by IMPLICIT_TRANSACTIONS ON, can I focus on Client tools setting IMPLICIT_TRANSACTIONS ON or ANSI_DEFAULTS ON or can this be set for the whole database affecting all clients? I can avoid the problem by changing my standard script header to SET ANSI_DEFAULTS ON SET IMPLICIT_TRANSACTIONS OFF but at which levels session, database, server can this setting be influenced? Edit: I found this in msdn it seems to be possible to set this on server level. In the Server Properties dialog (Connections Page) I found the option changed and scripted it: implicit transactions on: EXEC sys.sp_configure N'user options', N'2' GO RECONFIGURE WITH OVERRIDE GO implicit transactions off: EXEC sys.sp_configure N'user options', N'0' GO RECONFIGURE WITH OVERRIDE GO But executing exec('dbcc useroptions') in SSMS didn't reflect the change. The server setting seems to be somehow overwritten for such connections. Now I have to repose the question, are there clients which do not overwrite the server setting and can crash when the server setting is changed.

WAL Database log on separate disk good idea RAID10? Posted: 10 Aug 2013 02:30 PM PDT I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array. I use the WAL in sync-mode with a big buffer and do alot of bulk inserts and updates, so that buffer is hopfully used. Is it an good idea to split the log onto a extra disk, not in the raid10? I could stuff in a RAID with 2 disks, but would that really improve anything? I looking forward if someone could name a few pros and cons. Thanks alot

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

[MS SQL Server] Instance not started after update SP3?

---

Instance not started after update SP3? Posted: 09 Aug 2013 11:26 PM PDT Hi,Devlopment server.Version - SQL SERVER 2008 SP1, after update SP3 SQL services not started due to as following errors..[code="sql"] Error: 15173, Severity: 16, State: 1.2013-08-10 16:01:56.91 spid8s Login '##MS_PolicyEventProcessingLogin##' has granted one or more permission(s). Revoke the permission(s) before dropping the login.2013-08-10 16:01:56.91 spid8s Error: 912, Severity: 21, State: 2.2013-08-10 16:01:56.91 spid8s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15173, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.2013-08-10 16:01:56.91 spid8s Error: 3417, Severity: 21, State: 3.2013-08-10 16:01:56.91 spid8s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.2013-08-10 16:01:56.91 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.[/code]Could anyone guide me, what is forward path?Thanksananda

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL 2012] Permissions issue while deploying SSIS Project to SQL 2012

---

Permissions issue while deploying SSIS Project to SQL 2012 Posted: 09 Aug 2013 03:30 AM PDT I've built an SSIS Project created in SQL Server Data Tools which I'm trying to deploy to our Staging SQL 2012 Server, and I'm getting this error while trying the deploy. TITLE: SQL Server Integration Services------------------------------A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": System.ComponentModel.Win32Exception: A required privilege is not held by the clientSystem.ComponentModel.Win32Exception: at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread) at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName). (Microsoft SQL Server, Error: 6522)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=6522&LinkId=20476------------------------------Most of the articles mentioning this error suggest not to use local service accounts, which we're not. We're using domain accounts for all of the SQL Server services. Also one article also suggested adding the account used by the SQL Engine to the local Admin group since the deploy has to write to the file system, and I've done this as well with no luck.The thing is permissions are at least somewhat in place because the deploy created the Project Folders under the SSISDB catalog on the server. Update: I copied the Project to the SQL Server and tried deploying it using Catalog.Deploy_Project and it gave the same error. I'm logged in using a domain account that's in the Local Admin group, so I'm thinking it's a permissions issue from the SQL Service accounts. The domain accounts used by the SQL Engine Service and SSIS Service are both local admin accounts too, so I'm not sure what else to check.Any other suggestions on things to attempt? Thanks.Sam

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] parent Child Recursive count and distribution in sql server.

---

parent Child Recursive count and distribution in sql server. Posted: 08 Aug 2013 11:21 PM PDT [b]I am having following table and data :[/b]CREATE TABLE dbo.Emp ( EmpID int PRIMARY KEY, EmpName varchar(30), MgrID int FOREIGN KEY REFERENCES Emp(EmpID) ) CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID) ; INSERT dbo.Emp SELECT 1, 'President', NULL INSERT dbo.Emp SELECT 2, 'Vice President', 1 INSERT dbo.Emp SELECT 3, 'CEO', 2 INSERT dbo.Emp SELECT 4, 'CTO', 2 INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4 INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5 INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5 INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6 INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8 INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8 INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6 INSERT dbo.Emp SELECT 12, 'Tester 1', 11 INSERT dbo.Emp SELECT 13, 'Tester 2', 11 INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7 INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14 INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14 INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7 INSERT dbo.Emp SELECT 18, 'Tester 3', 17 INSERT dbo.Emp SELECT 19, 'Tester 4', 17 INSERT dbo.Emp SELECT 20, 'Tester 5', 17 -------------------------------------------------------------------With following sql i get the immediate child count for each parent.WITH ReportingTree (EmpID, EmpName, MgrID, Lvl) AS ( SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL UNION ALL SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1 FROM emp INNER JOIN ReportingTree on emp.MgrID = ReportingTree.EmpID ) SELECT T.EmpName, count(T.EmpId) FROM ReportingTree T JOIN emp P ON P.MgrId = T.EmpID group by T.MgrId ,T.EmpName ,T.EmpId;Now i want to distribute the tasks equally among the childs...For e.g.500 tasks are allocated to President...now President has only one child i.e vice President...so all 500 tasks will be allocated to him.now vice president has two childs i.e CEO and CTO...so 250 tasks for CTO and 250 tasks for CEO will be allocated...and so on..i.e tasks will be distributed depending on the number of childs...So Question are?1 . where to store the primary tasks allocation (can we store it in emp table for each most parent )?2.can we do it in single sql or in procedure .?Thanks!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] SSRS: way to add 100 columns in tablix,

---

• SSRS: way to add 100 columns in tablix,
• simple select - error
• time out error
• There is insufficient system memory in resource pool 'internal' to run this query. Error: 701, Severity: 17, State: 123.
• Permission to create Linked Server
• Reset identity seed on a large table
• Transaction Log
• Restore the mdf from a BAK to multiple drives possible?
• Replication DB - Dead lock issue
• Run querys on different database with Progress
• Can SQL Server Instances be hiddent....
• MAXDOP
• Locking in “Read Committed Snapshot”
• Patched but seemingly not Patched?

SSRS: way to add 100 columns in tablix, Posted: 09 Aug 2013 08:09 AM PDT Hi,I have report that have 100 buckets (for each day for 3+ month), so I need to compose it somehow, is there any way to automate this in SSRS 2008 ?This is hard coded stable number of columns, not tied to results.Probably there are some dynamics tools ?I also would like to add some background color formatting to all of this 100 columns, probably there is good way to automate this too.Appreciate your helpMario

simple select - error Posted: 09 Aug 2013 02:25 AM PDT Hi Team,am using below select statement, but getting error SELECT id, name, Count(id) AS T FROM testtestError:Column 'testtest.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Please help

time out error Posted: 08 Aug 2013 10:32 PM PDT System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.where can we check to resolve this issue ?

There is insufficient system memory in resource pool 'internal' to run this query. Error: 701, Severity: 17, State: 123. Posted: 09 Aug 2013 04:15 AM PDT I have a SQL instance that starting getting the "insufficient system memory" issue. Here is the server information:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (Hypervisor)System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.Physical Memory: 16GBMin Memory: 0GBMax Memory: 12GBError: 701, Severity: 17, State: 123.There is insufficient system memory in resource pool 'internal' to run this query.Here is the really strange issue: task manager os only showing 5GB being used. Don't know if that has anything to do with anything.When I look at sys.dm_os_process_memory I see the process_virtual_memory_low flag is 1. total_virtual_address_space_kb = 2,047MBvirtual_address_space_committed_kb = 1,810MBmemory_utilization_percentage = 100virtual_address_space_available_kb = 28MBprocess_virtual_memory_low = 1According to this there is VAS pressure. However, I have no idea how to correct it. Changing the SQL queries isn't an option for two reasons: 1) it is a vendor app and 2) nothing changed prior to this issue occurring and the application has been running for a year without problems. Below is the MEMORYSTATUS results. Does anyone have any idea what to do to fix this?Memory Manager KB---------------------------------------- -----------VM Reserved 1790320VM Committed 1751256AWE Allocated 0Reserved Memory 1024Reserved Memory In Use 0(5 row(s) affected)Memory node Id = 0 KB---------------------------------------- -----------VM Reserved 1787504VM Committed 1748552AWE Allocated 0MultiPage Allocator 55640SinglePage Allocator 104448(5 row(s) affected)Memory node Id = 32 KB---------------------------------------- -----------VM Reserved 1728VM Committed 1672AWE Allocated 0MultiPage Allocator 1600SinglePage Allocator 104448(5 row(s) affected)MEMORYCLERK_SQLGENERAL (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 24040MultiPage Allocator 10120(7 row(s) affected)MEMORYCLERK_SQLBUFFERPOOL (node 0) KB---------------------------------------- -----------VM Reserved 1658304VM Committed 1658304AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 504(7 row(s) affected)MEMORYCLERK_SQLQUERYEXEC (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 32MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLOPTIMIZER (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 1384MultiPage Allocator 72(7 row(s) affected)MEMORYCLERK_SQLUTILITIES (node 0) KB---------------------------------------- -----------VM Reserved 240VM Committed 240AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 304MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLSTORENG (node 0) KB---------------------------------------- -----------VM Reserved 14912VM Committed 14912AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 34376MultiPage Allocator 32280(7 row(s) affected)MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 1576MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLCLR (node 0) KB---------------------------------------- -----------VM Reserved 38592VM Committed 8472AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 1336MultiPage Allocator 568(7 row(s) affected)MEMORYCLERK_SQLSERVICEBROKER (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 176MultiPage Allocator 344(7 row(s) affected)MEMORYCLERK_SQLHTTP (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SNI (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 240MultiPage Allocator 16(7 row(s) affected)MEMORYCLERK_SNI (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 16(7 row(s) affected)MEMORYCLERK_SNI (Total) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 240MultiPage Allocator 32(7 row(s) affected)MEMORYCLERK_FULLTEXT (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 24MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLXP (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_BHF (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 240MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLQERESERVATIONS (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 3072MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_XE_BUFFER (node 0) KB---------------------------------------- -----------VM Reserved 4224VM Committed 4224AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_HOST (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 328MultiPage Allocator 32(7 row(s) affected)MEMORYCLERK_SOSNODE (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 11288MultiPage Allocator 10184(7 row(s) affected)MEMORYCLERK_SOSNODE (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 1520(7 row(s) affected)MEMORYCLERK_SOSNODE (Total) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 11304MultiPage Allocator 11704(7 row(s) affected)MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB---------------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 32MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_XE (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 4664MultiPage Allocator 128(7 row(s) affected)CACHESTORE_OBJCP (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 216MultiPage Allocator 16(7 row(s) affected)CACHESTORE_SQLCP (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 616MultiPage Allocator 232(7 row(s) affected)CACHESTORE_PHDR (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 440MultiPage Allocator 0(7 row(s) affected)CACHESTORE_XPROC (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 0(7 row(s) affected)CACHESTORE_TEMPTABLES (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 64MultiPage Allocator 0(7 row(s) affected)CACHESTORE_NOTIF (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 0(7 row(s) affected)CACHESTORE_VIEWDEFINITIONS (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 0(7 row(s) affected)CACHESTORE_XMLDBTYPE (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_XMLDBELEMENT (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_XMLDBATTRIBUTE (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_STACKFRAMES (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 8(7 row(s) affected)CACHESTORE_STACKFRAMES (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 8(7 row(s) affected)CACHESTORE_STACKFRAMES (Total) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 16(7 row(s) affected)CACHESTORE_BROKERTBLACS (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 96MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERKEK (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERDSH (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERRSB (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERREADONLY (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 32MultiPage Allocator 0(7 row(s) affected)CACHESTORE_BROKERTO (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)CACHESTORE_EVENTS (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 16MultiPage Allocator 0(7 row(s) affected)CACHESTORE_CLRPROC (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 40MultiPage Allocator 0(7 row(s) affected)CACHESTORE_SYSTEMROWSET (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 280MultiPage Allocator 0(7 row(s) affected)CACHESTORE_CONVPRI (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 144MultiPage Allocator 0(7 row(s) affected)CACHESTORE_FULLTEXTSTOPLIST (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 32MultiPage Allocator 0(7 row(s) affected)USERSTORE_SCHEMAMGR (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 12192MultiPage Allocator 936(7 row(s) affected)USERSTORE_DBMETADATA (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 912MultiPage Allocator 0(7 row(s) affected)USERSTORE_TOKENPERM (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 2624MultiPage Allocator 0(7 row(s) affected)USERSTORE_OBJPERM (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 1360MultiPage Allocator 0(7 row(s) affected)USERSTORE_SXC (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 208MultiPage Allocator 0(7 row(s) affected)USERSTORE_SXC (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 8MultiPage Allocator 0(7 row(s) affected)USERSTORE_SXC (Total) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 216MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_LBSS (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 104MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_SNI_PACKET (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 2040MultiPage Allocator 56(7 row(s) affected)OBJECTSTORE_SNI_PACKET (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 0MultiPage Allocator 56(7 row(s) affected)OBJECTSTORE_SNI_PACKET (Total) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 2040MultiPage Allocator 112(7 row(s) affected)OBJECTSTORE_SERVICE_BROKER (node 0) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 400MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_LOCK_MANAGER (node 0) KB---------------------------------------- -----------VM Reserved 4096VM Committed 4096AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 2416MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_LOCK_MANAGER (node 32) KB---------------------------------------- -----------VM Reserved 0VM Committed 0AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 24MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_LOCK_MANAGER (Total) KB---------------------------------------- -----------VM Reserved 4096VM Committed 4096AWE Allocated 0SM Reserved 0SM Committed 0SinglePage Allocator 2440MultiPage Allocator 0(7 row(s) affected)Buffer Pool Value---------------------------------------- -----------Committed 204760Target 204760Database 189898Dirty 18887In IO 0Latched 2Free 1734Stolen 13128Reserved 378Visible 204760Stolen Potential 181015Limiting Factor 11Last OOM Factor 0Last OS Error 0Page Life Expectancy 8427(15 row(s) affected)Process/System Counts Value---------------------------------------- --------------------Available Physical Memory 11782029312Available Virtual Memory 30199808Available Paging File 29535764480Working Set 1950310400Percent of Committed Memory in WS 100Page Faults 318585640System physical memory high 1System physical memory low 0Process physical memory low 0Process virtual memory low 1(10 row(s) affected)Procedure Cache Value---------------------------------------- -----------TotalProcs 8TotalPages 454InUsePages 26(3 row(s) affected)Global Memory Objects Pages---------------------------------------- -----------Resource 3321Locks 305XDES 127SETLS 4SE Dataset Allocators 8SubpDesc Allocators 4SE SchemaManager 631SE Column Metadata Cache 945SQLCache 55Replication 2ServerGlobal 29XP Global 2SortTables 3674(13 row(s) affected)Query Memory Objects (internal) Value---------------------------------------- -----------Grants 0Waiting 0Available 146163Current Max 146163Future Max 146163Physical Max 146163Next Request 0Waiting For 0Cost 0Timeout 0Wait Time 0(11 row(s) affected)Small Query Memory Objects (internal) Value---------------------------------------- -----------Grants 3Waiting 0Available 7308Current Max 7692Future Max 7692(5 row(s) affected)Optimization Queue (internal) Value---------------------------------------- -----------Overall Memory 1344528384Target Memory 1269694464Last Notification 1Timeout 6Early Termination Factor 5(5 row(s) affected)Small Gateway (internal) Value---------------------------------------- -----------Configured Units 16Available Units 12Acquires 4Waiters 0Threshold Factor 250000Threshold 250000(6 row(s) affected)Medium Gateway (internal) Value---------------------------------------- -----------Configured Units 4Available Units 4Acquires 0Waiters 0Threshold Factor 12Threshold 26451968(6 row(s) affected)Big Gateway (internal) Value---------------------------------------- -----------Configured Units 1Available Units 1Acquires 0Waiters 0Threshold Factor 8Threshold -1(6 row(s) affected)Memory Pool Manager Pages---------------------------------------- -----------Reserved Current 384Reserved Limit 181468(2 row(s) affected)Memory Pool (internal) Pages---------------------------------------- -----------Allocations 13438Predicted 49594Private Target 0Private Limit 0Total Target 194522Total Limit 194522OOM Count 0(7 row(s) affected)MEMORYBROKER_FOR_CACHE (internal) Pages---------------------------------------- -----------Allocations 2990Rate -919Target Allocations 147918Future Allocations 0Overall 164127Last Notification 1(6 row(s) affected)MEMORYBROKER_FOR_STEAL (internal) Pages---------------------------------------- -----------Allocations 10064Rate -1321Target Allocations 154992Future Allocations 0Overall 164127Last Notification 1(6 row(s) affected)MEMORYBROKER_FOR_RESERVE (internal) Pages---------------------------------------- -----------Allocations 384Rate 0Target Allocations 164127Future Allocations 36540Overall 164127Last Notification 1(6 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permission to create Linked Server Posted: 09 Aug 2013 05:20 AM PDT I have a user who has DB_Owner permission on a particular database.Now, that user needs to create a linked server for himself.what level of permission do I need to grant to that user at the server level for him to create Linked Server?

Reset identity seed on a large table Posted: 09 Aug 2013 07:51 AM PDT I need to reset the identity seed on 7.5 million records (someone added a need identity seed to an archive table instead of using the one that's assigned in the production table - now the archive table is out of sync with the production table). I want the last ID in the archive table to bump up against the first ID in the production table, that way moving and deleting records from production to archive will be easier. However I'm wondering how to reseed 7.5 m records without blowing up my transaction log. Any suggestions? TIA!!

Transaction Log Posted: 09 Aug 2013 07:47 AM PDT One of our databases transaction log file growing larger month by month.Before I increase the disk space I would like to understand is there way we can keep the transaction log file minimal other than shrinking.However, data is increasing I belive cause log file to grow.Could you please elobrate what other things can impact the trnasaction log file gowth other than maintenance plans etc.How should I do analysis for the disk space recommendations?Thanks and appreciated!

Restore the mdf from a BAK to multiple drives possible? Posted: 09 Aug 2013 04:57 AM PDT Hello - I have 2 BAK files to restore to 2 drives (1 for the mdf, 1 for the logs)I'm running out of room for the mdfs and can't restore both databases.Is it possible to restore the mdf of one of the databases to 2 different drives?For example, both databases together need 105 GB and I have a 100 GB drive. Is there any way to restore and have 5GB sit on another drive?I could not find an example of this or see the option in the GUI.I don't have the option of changing the backup process. I receive the BAK files from another agency that won't modify their procedures.ThanksDave

Replication DB - Dead lock issue Posted: 29 Jun 2013 03:32 PM PDT Hi,We have One Database which is a Replication Db, sometimes during beginning of the month, lot of user activities going and we having Dead lock issue.Any one has idea how to prevent the dead lock in Replication DB?We have to manually kill the user session one by one but if someone having same kind of issue and have any idea?Thanks,

Run querys on different database with Progress Posted: 05 Aug 2013 09:43 AM PDT Hi guys,I am using ODBC to connect Progress from Sql server 2008, But I need to connect with two differents databases on the same query, It is possible?I am using Progress OpenEdge 11.2Any help would be greatly appreciated.Thanks,Sandra

Can SQL Server Instances be hiddent.... Posted: 09 Aug 2013 04:00 AM PDT I would like to know here that .. Is there any possiblity to hide SQL Server Instances... Please clarify me.Thanks in advance !!!

MAXDOP Posted: 08 Aug 2013 11:50 PM PDT I know this is a very subjective topic and the actual figured is best gained through testing etc but I just wanted to ask if there is any truth to the following:ideal maxdop = total cores / number of NUMA nodes * 0.75I have found this formula in a few places but havent found with it any explanation or reasons how it works, could anyone explain?

Locking in “Read Committed Snapshot” Posted: 08 Aug 2013 08:36 PM PDT IF update command is run on a table with "Read Committed_Snapshot" isolation level and Commit is pendingeg: 1) update table1 set col1 = col1 + 1 where PKcol < 32) update table1 set col1 = col1 + 1 where PKcol = 33) update table1 set col1 = col1 + 1 where NonPKcol < 34) update table1 set col1 = col1 + 1 where NonPKcol = 35) update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5(In above case PKcol is primary key in table and NonPKcol is a non-primary key)then whether Update is locked for only rows satisfying 'where' condition ? (is it based on value or index or Primary column ?)

Patched but seemingly not Patched? Posted: 08 Aug 2013 09:29 PM PDT A colleague recently applied SQL Server 2008 R2 SP2 to one of our servers.Through Management Studio it shows correctly as 10.50.4000 but if we run the installer again, it hasn't greyed out the tick and shows the patch as "Not Upgraded".Has anyone seen this before and know what's wrong?

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Joining two tables: Provided/Budget

---

• Joining two tables: Provided/Budget
• SQL Server 2005 W2008 r2
• SSIS SQL - to AD update question

Joining two tables: Provided/Budget Posted: 09 Aug 2013 05:12 AM PDT Hi all. I have written two programs; One which sum the total cost for services provided and one which pulls in the projected budgets for the services. The budgets can be renewed once a year or every six months. I'm having trouble joining to the programs together so I can calculate the % of budgets used for each waver service by month.Projected Budget Program[code="sql"]SELECT ChildID,WAIVER_SERVICES,START_DT,END_DT,RATE_PER_UNIT_OF_SERVICE,PROJECTED_COST_PER_MONTHFROM [ECMS_BACKUP].[dbo].[B2H_DSP]where CLT_NBR=10177and WAIVER_SERVICES NOT IN (1,10,11,12) and WAIVER_PROGRAM=2[/code]The Budget data set I bring back looks something like this:[code="sql"]CREATE TABLE Budget ( ChildID int, WAIVER_SERVICES int, START_DT date, END_DT date, RATE_PER_UNIT_OF_SERVICE int(30), PROJECTED_COST_PER_MONTH ); INSERT INTO Budget VALUES (10177, 2, '06/01/12', '06/01/13', 13.23, 158.76),(10177, 3, '06/01/12', '06/01/13', 13.23, 211.68),(10177, 5, '06/01/12', '06/01/13', 19.45, 466.8),(10177, 8, '06/01/12', '06/01/13', 236.7, 473.4),(10177, 9, '06/01/12', '06/01/13', 19.55, 312.8),(10177, 2, '06/03/13', '06/03/14', 13.23, 158.76),(10177, 3, '06/03/13', '06/03/14', 13.23, 211.68),(10177, 5, '06/03/13', '06/03/14', 19.45, 466.8),(10177, 6, '06/03/13', '06/03/14', 55.68, 222.72),(10177, 8, '06/03/13', '06/03/14', 236.7, 473.4),(10177, 9, '06/03/13', '06/03/14', 19.55, 312.8)[/code]Notice the START_DT and END_DT are not set set monthly but yearly.Services Provided Program[code="sql"]select a.ChildID,b.WAIVER_SERVICES, month(a.DOSStart) as Month, YEAR(a.DOSStart) as Year,sum(b.RATE_AMOUNT)as totalfrom [ECMS_BACKUP].[dbo].[B2H_SummaryForms]aleft JOIN ECMS_BACKUP.dbo.B2H_RATE b ON a.RateCode=b.RATE_CODEwhere DOSStart>='2013-01-01' AND DOSStart<'2013-06-30'group by ChildID, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICESorder by childid, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES[/code]The Services Provided data set I bring back looks something like this:[code="sql"]CREATE TABLE Provided ( ChildID int, WAIVER_SERVICES int, Month int, Year int, total int, ); INSERT INTO Provided VALUES ('10177', 3, 3, 2013, 13.23),('10177', 5, 3, 2013, 77.8),('10177', 8, 3, 2013, 236.7),('10177', 9, 3, 2013, 19.55),('10177', 3, 4, 2013, 13.23),('10177', 5, 4, 2013, 19.45),('10177', 5, 5, 2013, 19.45),('10177', 8, 5, 2013, 236.7),('10177', 9, 5, 2013, 19.55),('10177', 5, 6, 2013, 19.45),('10177', 8, 6, 2013, 236.7),('10177', 3, 8, 2012, 17.2),('10177', 3, 10, 2012, 13.23),('10177', 8, 10, 2012, 473.4),('10177', 9, 10, 2012, 19.55),('10177', 2, 11, 2012, 13.23),('10177', 3, 11, 2012, 13.23)[/code]Again, I am have trouble joining these two data sets to get something that looks like thisChildID WAIVER_SERVICES Month/Year services_provided Budget % used 10177 3 12/2012 125 125 100% 10177 3 1/2013 75 125 60%10177 3 2/2013 100 175 57% 10177 9 1/2013 50 50 100%10177 9 5/2013 800 950 84%Any assistance is greatly appreciated!

SQL Server 2005 W2008 r2 Posted: 09 Aug 2013 02:24 AM PDT Question: when creating a failover cluster I made the mistake of selecting available storage which had all the drives under it. When I tried to install the second instance i didn't have any storage options. I believe that when I get to the step of cluster groups i should have group options of dtc, group 0 and group 2 which has separate data drives for instances. Is this correct?Secondly I thought when the virtual server is created it would appear in the cluster manager, What makes visible? When it was there before the install said invalid name since was already there.Confused??

SSIS SQL - to AD update question Posted: 09 Aug 2013 12:57 AM PDT fixed

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610