Wednesday, July 31, 2013

[SQL 2012] SQL Server TempDB ballooning to more than 75 GB

[SQL 2012] SQL Server TempDB ballooning to more than 75 GB

SQL Server TempDB ballooning to more than 75 GB

Posted: 30 Jul 2013 09:43 AM PDT

Hi,In one of our database while executing a procedure serving an important business logic, which has 11 joins, then a filtering on a specific column, sorting using order by on a specific column. In the join condition, as I see them separately, there are millions of rows fetched. Now I see that TempDB is ballooning to 75 GB size, which is the available disk space and the query fails.What could be the possible reason:- Is the TempDB, behaving incorrectly, can I really expect that kind of size (75 GB).- If the TempDB behavior is correct, is there something I can do to mitigate the situation, will the faster execution of the query, having more statistics, relevant index, more seeking than scanning of index / table, will that help is solving the situation.I know a possible situation would be relocate the db to a much a disk space, but I want to figure out the tuning options first, since I do not know, what is the maximum size TempDb will bloat up toAny relevant suggestion would be great.thanks,Mrinal

SQL WHERE clause in Excel? HELP!!!

Posted: 31 Jul 2013 02:34 AM PDT

I have three variables on an [red]Excel form [/red] (version 2010) that connects to a Teradata database. The first variable is a date format (DateworkedF and DateworkedT) the other two are text fields. (StatusX and ErrorTypeX)I can return a record set from Teradata using either "Status" or "ErrorType" but can not get the date from/to to work?? Also, if i leave any of the fields blank, i get no records returned??I want to be able to search on any or all of these fields. (If the field is blank return all values)Can't figure out the syntax ???[code]Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _"FROM UD402.JD_MCP_MASTER WHERE " & _"(DATE_WORKED >= #" & DateworkedF & "# Or #" & DateworkedF & "# IS NULL)" & _"AND (DATE_WORKED <= #" & DateworkedT & "# Or #" & DateworkedT & "# IS NULL)" & _"AND (STATUS = '" & StatusX & "' OR '" & StatusX & "' IS NULL)" & _"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL);"

Annual Count

Posted: 31 Jul 2013 12:26 AM PDT

I am working on an HR project and I have one final component that I am stuck on. I have an Excel File that is loaded into a folder every month. I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).I then combine it with another table in a view. I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it's a table of all employees, positions, hire dates, term dates etc).Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count. So the data will look like this. |Year|HistoricalHC|NumbTermedEmp||2010|447 |57 ||2011|419 |67 ||2012|420 |51 |The data is in one table labeled [EEMaster]. To test the count I have the following. SELECT COUNT([PersNo]) AS HistoricalHCFROM [dbo].[EEMaster]WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'this returns the HistoricalHC for 2013 as 418.SELECT COUNT([PersNo]) AS NumbOfTermEEFROM [dbo].[EEMaster]WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'This returns the Number of Termed employees for 2013 as 42. I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into. |Year|HistoricalHC|NumbTermedEmp||2010|447 |57 ||2011|419 |67 ||2012|420 |51 |I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from. (so on Dec 31st this package will run and add |2013|418|42| to the next row, and so on. Thank you in advance.

SQL Instance Name not appearing in drop down list on Cluster Node Configuration Window

Posted: 29 May 2013 03:50 PM PDT

I am trying to install the MS SQL Server 2012 Failover Cluster on Windows Server 2012 . I successfully Installed the Failover cluster instance on my primary node. But when I am running the installation process on passive node to add node in the failover cluster I am stuck with very unique kind of issue.I am following the Standard process of Installation and I am getting the same windows for each next process, but after License Agreement window when I get the Cluster Node Configuration window, then in "SQL Server Instance Name" drop down box I am not getting the Name of Instance which is already installed on the primary node. But this Instance complete information is appearing in below given box.Only issue is Instance name is not appearing in the drop down list, that's why I am not able to select and when I click on next it trough error and do not proceed.Please can you tell me the possible cause behind this issue, and I am first time doing cluster installation in SQL Server 2012.I truly appreciate your help.

What is the performance point at which it makes sense to get Enterprise instead of Standard

Posted: 22 Jul 2013 02:09 AM PDT

We are looking to build a SQL Server server. We are looking to get SQL 2012, but not sure which version to get. At this point, I think Standard is all we need, but to make sure we cover our bases, I need to know at what point does it make sense to get Enterprise. Is there a number of users or a database size, amount of usable memory or something else that would be a reasonable indicator that we should get Enterprise?

CREATE Login doesnt recognise a service account

Posted: 30 Jul 2013 05:38 AM PDT

I am unable to use T-SQL statement "CREATE LOGIN xxx\yyy FROM Windows" and create a Login in SQL Server. I can manually go through SSMS and create the login by doing 'New Login' but when I try to do with T-SQL, it says that xxx\yyy is not a windows user or group and errors out. When I do manually through SSMS, it does recognise that account but not with T-SQL.What am I missing?

AlwaysOn Synchronous Replica in Synchronizing State

Posted: 30 Jul 2013 10:23 PM PDT

I have an AlwaysOn secondary node that is set to synchronous commit that is stuck in the Synchronizing state. This had previously been working fine but I needed to restart the secondary node because we added another processor to the virtual machine. The server stated fine but it will not synchronize. How can I troubleshoot this issue? There is very limited information from Microsoft on how to determine what the problem is. I have checked the AlwaysOn Health Events but there is nothing that suggests a problem. I can't believe that this is due to network latency because these servers are beside each other on the same subnet and had previously been synchronizing fine. Could it be due to the addition of the CPU? Any suggestions appreciated.

Error Configuring Master Data Services SQL Server 2012

Posted: 29 May 2012 12:10 PM PDT

Hi AllI would appreciate a bit of help with this known issue.Jose Chinchilla (http://sqljoe.wordpress.com/2011/11/29/sql-server-2012-master-data-services-error-the-required-svc-handler-mappings-are-not-installed-in-iis/) pointed out that this was a known issue with RC0.When one tries to configure Master Data Services the first screen is shown indicating that IIS is not configured correctly and that a .svc file is not configured correctly.This is the message[b]"Before creating an MDS web application, you must correct the following errors:Internet Information Services (IIS) is not configured on this server.The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284.For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=215355.[/b][b]This issue has been communicated to Microsoft via Connect ID: 701993 and is documented in the Technet Article "Troubleshoot Installation and Configuration Issues (Master Data Services in SQL Server 2012)"[/b]Now, my operating system is Windows 7 Ultimate.Further, I believe that I have removed all of the Enterprise edition RTM trial from my PC and I have installed the DEVELOPER edition copy of 2012 that I have purchased.The plot thickens...I configured IIS to the settings as recommended on page 16 of Tyler Graham and Suzanne Selhorn's book. Should anyone have run into this issue and have any ideas, I would be most grateful.regards Steve SimonP.S. MDS 2008 work perfectly on my machine( no issue).

How to loop throught prameters to use the same sqlquerys

Posted: 30 Jul 2013 04:36 AM PDT

create table #temp(ID int,Type nvarchar(10),Value int)insert into #tempvalues (1, 'A', '25')insert into #tempvalues (2, 'A', '47')insert into #tempvalues (3, 'B', '3')insert into #tempvalues (4, 'C', '3')insert into #tempvalues (5, 'D', '7')-- use the same sql with type A and B , first only with A and then only with B-- parameter/loop ?-- do something with type A -- STARTSELECT ID, Type, Value + 1 from #temp where type = N'A'-- more then one sqlquerys-- cleanup-- END-- use the same sql with type B-- STARTSELECT ID, Type, Value + 1 from #temp where type = N'B'-- more then one sqlquerys-- cleanup-- END-- Do something else with type C and D/Clas

Replication - Adding article to a publication causing the snapshot of all other articles

Posted: 30 Jul 2013 12:44 PM PDT

Hi guys,Adding an article(table) to a publication, and then running the snapshot agent should create a snapshot only for the new article. However, sometimes replication decides to re-snapshot every article in the publication. So, I have 2 questions:1. Why sometimes all articles are re-snapshotted if only one article was added?2. Is it possible to know beforehand which articles are marked to be snapshotted next time the snapshot agent runs (such as in a DMV or system table) ?Thanks a lot.

Always On database as transactional subscriber

Posted: 30 Jul 2013 12:04 PM PDT

[u][b]What's the background of this question?[/b][/u]I'm having my first project using Always On High Availability Groups. We want to fill our databases using transactional replication and we are dealing with some special behaviour that makes us some troubles.It seems that there is nearly no information about some things and so I'm wondering if we are the first and only company on this planet using this constellation?

Partion Diffrent Connection Strings

Posted: 23 Jul 2013 08:09 PM PDT

I have 3 partion and ı want to change2 partion to diffrent connection is it possible?

Need a help in MCSA SQL Server exam 2012 70-461, 462, 463

Posted: 30 Jul 2013 10:03 AM PDT

Hello friends,after getting good experience at current workplace, I am planing to get certified in MS SQL SERVER 2008/2012.I went through the webpage that provide the exam information.I am looking for any preparation material, or web site that can help me with the preparation.because, I can not afford the books, which are mentioned in the website for preparation of the exam.Please let me know where I can start reading or get some practice materials.thanks in advance.Pratik

Management Studio and Multi-Subnet Availability Group Listener

Posted: 30 Jul 2013 06:03 AM PDT

Hello,Does anyone know if SSMS is able to use the MultiSubnetFailover parameter to connect to an AG listener? I tried listing it in the advanced connection properties and it didn't work (Management Studio version 11.0.2100.60). We have a multisubnet availablity group with a listener and after much effort, we are able to connect via SQLCMD with the -M and -l30 flags, but I can't connect to the server via the listener name through management studio. I can connect to the individual servers, but the listener name times out even if I set the connection timeout to 60 seconds. It seems odd that SSMS wouldn't support the new connection parameters.Thanks!Jason

What happened to retail purchase of SQL Server?

Posted: 30 Jul 2013 03:46 AM PDT

Once upon a day, armed with the appropriate MS part number, you could approach any number of software retailers and buy SQL Server and they would ship you a shrink-wrapped box. Now it appears the only way to 'purchase' SQL Server is to belong to one of the MS licensing programs. Is my understanding correct? I work for a small company and just need to get SQL Server 2012 Standard edition installed on a 4 core box. I'd rather not have to understand and enter into a licensing program that may not be in my best interest next month or next year. What am I missing? Am I making this too difficult?Thanks,Alan
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog