InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 17:28:22 GMT20http://www.sqlservercentral.com/Forums/Topic1454801-2799-1.aspxWhat are the benefits of upgrade to SQL Server 2012?Can someone direct me to webcast benefits upgrade SQL Server 2008 R2 to SQL Server 2012 Thanks in advanced.-EdwinMon, 20 May 2013 21:30:15 GMTEd7http://www.sqlservercentral.com/Forums/Topic1456496-2799-1.aspxThe requirements for AlwaysOn state that WSFC that contains your replicas needs to be in the same domain. Does this mean the same AD domain Forest or subdomain? Would I be able to have a replica in x.domain.com and y.domain.com?Thanks!Fri, 24 May 2013 07:34:28 GMTSmiley77http://www.sqlservercentral.com/Forums/Topic1456324-2799-1.aspxDear Team, We are recieving an error while using Application through Terminal Service. Operating on which we have configured terminal serverO/S Windwos Server 2008R2 ENT Edition. As per our analysisif same DB server with MS SQL 2012 is used by connecting the application placed locally then no issue occurs.PLease see the error through given URL.[b]http://tinypic.com/r/2r4s9zd/5[/b]RegardsNaveedFri, 24 May 2013 02:20:27 GMTnaveed.amirhttp://www.sqlservercentral.com/Forums/Topic1456123-2799-1.aspxHey guys - I'm trying to create a custom Function in SQL 2012 (by following an example in a book) and it's not working. I'm hoping someone can help.This is my custom function. No errors. It executes fine. Simple right?**********************************CREATE FUNCTION fncNotAvailableDisplay (@strInputString varchar(50))RETURNS varchar(20)ASBEGIN If @strInputString IS NULL SET @strInputString = 'Not Available' RETURN @strInputStringEND**************************When I try to use it in a query, I get an error.SELECT Address1, Address2, fncNotAvailableDisplay(Address3) FROM Customers[i]'fncNotAvailableDisplay' is not a recognized built-in function name.[/i]I am using it within the same database. Thoughts??Thu, 23 May 2013 11:25:28 GMTRedBirdOBXhttp://www.sqlservercentral.com/Forums/Topic1455995-2799-1.aspxCan anyone recommend a good step by step guide/article to setting up high availabiltyThu, 23 May 2013 07:14:43 GMTMarvinTheAndriodhttp://www.sqlservercentral.com/Forums/Topic1404759-2799-1.aspxWe are having a tough time with this, and I don't know who to ask since it overlaps with SQL and SharePoint, so I am hoping someone here has some insight for us![b]Quick version:[/b]SharePoint 2010 has a special app called [url=http://technet.microsoft.com/en-us/library/ff686793(v=office.14).aspx]PrerequisiteInstaller.exe[/url] that installs SP2010 prerequisites, including three SQL Feature Pack components: SSRS Add-in, ADO and SQL Native Client. But those prerequistes seem to be SQL Server [b]2008[/b]-specific. We are using SQL [b]2012[/b], so we want to get PrerequisiteInstaller.exe to work with the SQL 2012 components. Does anyone know how to do this?[b]Long version:[/b]We have already built a nice SharePoint 2010 slipstreamed .iso with the latest Service Pack, Cumulative Update, and the SQL 2008 R2 prerequisites.But now we would like to build a new .iso with the SQL 2012 with SP1 prerequisites for SharePoint 2010. We tried this, but it fails.It seems the problem is with the SharePoint 2010 PrerequisiteInstaller.exe not recognizing the new SQL 2012 SP1 files.The three specific files are the SQL Native Client, ADO, and SSRS Add-in. We tried to just use the same command line switches, but point to the new files. Our command lines look something like this:/SQLNCli:file - Install Microsoft SQL Server 2008 Native Client from file/SQLNCli:"C:\SP2010 Prerequisites\Microsoft SQL Server 2008 R2 SP1 Native Client\sqlncli.msi"/ADOMD:file - Install Microsoft SQL Server 2008 Analysis Services ADOMD.NET from file/ADOMD:"C:\SP2010 Prerequisites\Microsoft SQL Server 2008 R2 ADOMD.NET\SQLSERVER2008_ASADOMD10.msi"/ReportingServices:file - Install SQL 2008 R2 Reporting Services SharePoint 2010 Add-in from file/ReportingServices:"C:\SP2010 Prerequisites\Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010\rsSharePoint.msi"Just replacing the files with the corresponding SQL 2012 SP1 files does not seem to do the trick.Might any of you encountered this? If so, might you know how to get this to work? We really just want to get the SharePoint 2010 PrerequisiteInstaller.exe to recognize the new SQL 2012 SP1 files.Any help or insight appreciated!Wed, 09 Jan 2013 07:12:39 GMTjpSQLDudehttp://www.sqlservercentral.com/Forums/Topic1449216-2799-1.aspxHelloI am not sure if this is a SQL issue or Windows Cluster issue, but am trying to rule out it being a SQL issue.When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.Anyone ever run into this before?QsacFri, 03 May 2013 08:00:38 GMTSteve Q-426954http://www.sqlservercentral.com/Forums/Topic1424017-2799-1.aspxHi! I am new in this forum and so excited to meet people who live and breath SQL!Besides being new in this forum, I am new to SQL as well so forgive me if I will ask newbie and "stupid" questions :-)Now on to my problem.I am trying to install an SQL server 2012 developer edition with SP1 X64 to my local machine and received this error "SQL server media not supported on a X86 system. To continue, run the SQL server setup media that matches your system"What have I done wrong?A few more information prior to the installation :- Operating System is Windows 8 on a dual boot MacBook pro- I allocated 81GB space for Windows system. No application installed yet. SQL would be the first- I uncompressed the .iso file to a USB drive and to my hard drive then run setup.exe from both but still had the same problemI suspect that it is because I am trying to install through a different media other than a DVD disc but I do not have available disc right now so I took my chance. I wasn't successful. What is the work around with this problem?Has anyone encountered this problem before? I need all the help please because I am aiming to take certification exams and this is my initial step so I can practice and review on my spare time.All help will be appreciated.thanks!Tue, 26 Feb 2013 05:15:03 GMTRGThttp://www.sqlservercentral.com/Forums/Topic1426555-2799-1.aspxSo I have created a new Tabular cube on 2012, deployed it for a while. And created a few new partitions via SSMS in the server to cover all the data. Now I have made some changes in the project, planning to deploy it to the server, but can NOT find an option to ignore existing partitions (that are not defined in the project itself) I have tried it on a test db, and no matter what i do... the project seems to deploy its definition, and overwrite what's on the server. meaning all the partitions that i created after deploy are WIPED! I have billions of rows of data, so reprocessing all the missing partitions are really not preferable... and managing the partitions in project but not in ssms is also not preferable as we typicall use scripts to add / manage partitions after it goes livewe have found some blog posts about changing .deploymentoptions file... but we don't know what value we should change to (not in BOL).. we changed it to "RetainPartitions" as a test.. but that doesn't workanyone has been through this? thanksMon, 04 Mar 2013 19:05:07 GMTboxtahttp://www.sqlservercentral.com/Forums/Topic1455295-2799-1.aspxI am involved in project of upgrading database servers to SQL 2012 using AlwaysOn functionality. It is still in planning phase and i am currently working on capacity planning.My question are1. Is there any restriction on number of databases in one availability group? 2. Current SQL 2008 R2 instance is hosting 600 databases, has anybody experienced any issues with these number of databases running on 1 SQL instance? I know technically i can have 32k databases on 1 instance but in reality considering transaction log backups, agent jobs running on each database and other DB maintenance tasks, it will be a challenging task for DBA maintaining these databases. Question is, has anyone worked with these number of databases before and what were their experiences.3. Few databases are being replicated to other database servers, what special care do i need to take in order to setup replication with AlwaysOn?Tue, 21 May 2013 22:00:42 GMTAnam Vermahttp://www.sqlservercentral.com/Forums/Topic1455606-2799-1.aspxAfternoon allI'm trying to restore three databases which are all in the primary node of a SQL2012 AlwaysOn Availability Group. I know I can do this by restoring to the primary, backing up full and log from the newly restored primary and restoring those onto each secondary, but is there a more efficient way? I wondered whether I could restore my full source backup onto the primary, take a log backup of the primary, and then restore the full source backup and the log backup from the primary onto the secondary. The DBs are large so rather than burn hours trying it, I thought I'd ask here first whether that's possible, or whether there's another way. There is one confusing answer [url=http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/359a635f-442c-49e6-8ba0-84b638a44e74]here[/url] that suggests it can be done via tail log and log backups, so I had a crack at that but it didn't work as there was a break in the LSN chain large enough to drive a truck through.ThanksKevWed, 22 May 2013 10:29:57 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic1455587-2799-1.aspxI've been trying to download a CU for SQL 2012 all day but am hitting a wall. Using SQL 2012 CU5 as an example, I go to the relevent MS page, click on the button to request the hotfix, get taken to the next page to enter my details and get a lovely page of blankness.I've tried several CU's for 2008 and 2012 (and multiple browsers, machines and locations) but keep getting the same blank screen issue.Does anyone have a copy of the CU that they can point me at a download for, I've got a call back due with MS, but this is causing me problems as I can't proceed with the install of SCCM 2012 SP1 until I get CU2 or above working. I wouldn't have a problem if SQL 2012 SP1 worked, but I guess MS decided to delay that working with SCCM for now.ARGH!!!!! and thanksWed, 22 May 2013 09:46:41 GMTMike Metcalfhttp://www.sqlservercentral.com/Forums/Topic1455364-2799-1.aspxHi, I regularly (every month or so) get the error "The target principle name is incorrect. Cannot generate SSPI context" when trying to remotely connect to my SQL 2012 instance. The SQL service is running using a managed service account. I understand this error can occur when the service account cannot authenticate with AD properly. Looking at the properties of the managed service account, the password for the account was automatically changed this morning - just when the error started. I am therefore assuming that this problem is something to do with this, but I am not sure. I can fix the issue by restarting the SQL Service, but that is not ideal. Does anyone have any pointers as to how I can stop this problem from happening again?Thanks.Wed, 22 May 2013 02:44:11 GMTMaddavehttp://www.sqlservercentral.com/Forums/Topic1455512-2799-1.aspxHow do I grant a regular user or group the same rights to view the SSIS execution reports as say the sysadmin role?I need to figure out how to give users the visibility of those reports without actually granting sysadmin.Wed, 22 May 2013 07:42:28 GMTtim.cloudhttp://www.sqlservercentral.com/Forums/Topic1455442-2799-1.aspxWe have an Oracle Database with a size of about 80 GBs with 8 Redo Logs of 300 MBs each. Just wondering what would be a good starting point in SQL Server 2012 for this same database? I know there are more factors that go into trying to derive the Database Size but this is all I have right now. (I also will try to get the vendor input.) It is a new application (Student Information System for a large school district; about 60,000 students, 4,000 staff.) Is there some kind of guideline, such as set your tempdb to 40-50% of user database size and set log files to 25-30% of user database's data files (primary and secondary) size?Thanks in advance, KevinWed, 22 May 2013 06:01:19 GMTkevinsql7http://www.sqlservercentral.com/Forums/Topic1452803-2799-1.aspxIf SQL Server 2012 is installed with all defaults selected, is there a way (post installation) to change the data directories settings?Thanks in advance, KevinTue, 14 May 2013 13:36:36 GMTkevinsql7http://www.sqlservercentral.com/Forums/Topic1455154-2799-1.aspxUSE [JobPortal]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER Procedure [dbo].[GetAllJobsSearched] -- Add the parameters for the stored procedure here @Title varchar(250), @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int WITH RECOMPILE AS DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));IF (@Title = '') SET @Title = NULL;WITH EMPLOYMENT_OPPORTUNITIES_CTE(rownumber, id,title,contactperson,lastmodified,description,workexperiencetypeid, workexperience,jobtypeid,AcademicExperienceTypeId, industryid,industryid2,industryid3,salaryminid,salarymaxid, jobTitle,city,state,PostalCode,positions,deadline)AS( SELECT top 4000 ROW_NUMBER() OVER (ORDER BY lastmodified DESC) RowNumber, e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid, isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3, salaryminid,salarymaxid, isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle, isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city, isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state, c.PostalCode, positions, substring(cast(e.lastmodified as varchar(20)),1,12) as deadline --ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber from EmploymentOpportunities e With (nolock) inner join companys c on e.officeid=c.id where (@Title IS NULL or title = @Title) and (@Industry = 0 OR e.industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) and (@State = 0 OR c.RegionId = @State) and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary))SELECTid,P1.lastmodified as deaddate,P1.Title,P1.city + ', ' + state + ' ' + PostalCode as Location,contactperson as ContactPerson,description as Description,workexperience,jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,salaryminid as salary,salarymaxid as maxsalary,jobtitle as jobTitle,positions as Positions,deadline, RowNumber,(select max(RowNumber) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (SELECT RowNumber,id,lastmodified,title,city,state,PostalCode,contactperson,description,workexperience,jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,jobtitle,positions,deadlineFROM EMPLOYMENT_OPPORTUNITIES_CTE ) p1The above query in its current state searches 600k records in approx. 300 ms.I have tried numerous ways to do dynamic sorting on LastModified, which is a date field, or Title, which is a Job Title field, or City, which is a City Name Field.Regardless of which method I have tried, the dynamically sorted query always takes approx. 8 seconds !At the moment I am doing the sorting in C#, but would like to be able to do the sorting in SQL, if I can get the time under one second, and the data sorted in the correct order. Any suggestions would be greatly appreciated.Thanks The above fields are also sorted ascending or descending.Tue, 21 May 2013 12:05:08 GMTisuckatsqlhttp://www.sqlservercentral.com/Forums/Topic1455202-2799-1.aspxHello,I have a two questions about permissions when installing SQL 2012.I’ve created additional drives for Data, Logs and tempdb on my new SQL server. I install SQL 2012 SP1 (on Windows 2012), use the defaults for the service accounts (nt service\mssqlserver), add the local administrators group as server admins and choose mixed mode authentication.The install goes just fine but after, I cannot login to management studio on the sql machine using my domain account when I’m on the sql machine (even though I’m part of a group that is part of the local admins group). I can login when I use management studio on another machine.If I add my teams group as a login, then I am able to use management studio locally. Is this expected? (using sa locally is fine and that's how i was able to add the active directory group)The other thing I don’t understand is why I don’t have permission to browse the “data” directory on any of my additional drives via windows explorer. From the Windows desktop, if I try to browse to L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\data, I will get an error saying I don’t have permission to access “data” but I can click the button to get access to it (which I haven’t clicked yet). I have no problems drilling down to that folder, just getting into it is the problem.If I try to browse to the machine via the network, ie. \\10.2....\l$\Pro..... Then I can access the folder no problem and it appears the owner if the local administrators group. If I login to the machine with the Administrator account, I can access the folder. If I use another local account in the local administrators group, I get the same error as my domain account.Is this expected or do I need to set some permissions before installing SQL? (This same issue affected my ability to browser via management studio as well)This is all a brand new setup for it's just a plain vanilla install of Windows 2012 (UAC disabled) and a new install of SQL 2012.If anyone can provide some insight I would really appreciate it.thanksTue, 21 May 2013 13:47:58 GMTksdjfhhttp://www.sqlservercentral.com/Forums/Topic1455172-2799-1.aspxCurrent partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?Tue, 21 May 2013 12:54:30 GMTLexahttp://www.sqlservercentral.com/Forums/Topic1455052-2799-1.aspxI copied a full backup of my SQL Server 2008R2 production database to the directory C:\Temp on my SQL Server 2012 test server (both Windows Server 2008R2), and tried to restore it to a newly created database. I selected the device and located the backup file, but do not see it, so pasted the file name in. Then selected the destination database (the new SS12 db), selected "Overwrite the existing database", but do not see a backup set. "OK" is grayed out, and the top of the Restore task screen says "No backupset selected to be restored".Then I copied the same backup file from C:\Temp to C:\Backup, on the same server, and the Restore screen can now see the backup file. I also copied it to C:\Restore and the SQL Server 2012 likes that directory too. Why must the restore file not be in C:\Temp? Or am I not understanding the real problem? Thank you for your help.Tue, 21 May 2013 08:40:41 GMTAlan Spillerthttp://www.sqlservercentral.com/Forums/Topic1454873-2799-1.aspxHi allI have an AlwaysOn Availability group configured between 2 nodes (Synchronous)Automatic failover was working fine until recentlyI can failover between the nodes manually but automatic failover doesn't seem to be working. In my earlier test, I would shut down the SQL Service on the primary and within seconds, the secondary replica would take over. Recently I have performed the same test and the secondary replica enters the resolving state and the DB in unavailable.I have tried everything here: [url]http://support.microsoft.com/kb/2833707[/url]The only change I made was changing the availability mode from Synchronous to Asynchronous - Could that be the cause?ThanksTue, 21 May 2013 02:34:56 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1418043-2799-1.aspxGreetings all!So I want to purchase the 2012 developer edition, and while I know I could go to Amazon or newegg or an online vendor, I was hoping to purchase either 1) A local copy from a store or 2) Purchase a digital version I have searched the online stores of local stores to no avail and I am not sure where one would physically be able to buy a copy of Developer edition. I tried a few google searches as well for a Digital copy. I will order a copy here shortly from Amazon most-likely however I have a few needs to get this today to get prepared for next week. Is there anyway I can do this?Sat, 09 Feb 2013 12:36:47 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic1453897-2799-1.aspxHi,During some performance tuning and optimization I performed at a client, I discovered what appears to be strange behavior and I just wanted to ask the community for any comments.The behavior is related to the IO's generated when updating a table with covering indexes.As a test, I setup the following scenario:Create a simple table with three columns1. A primary key (clustered).2. A column to index on.3. A column to include.I create three of these tables1. A table with only a clustered IX on column 12. A table with a clustered IX on column 1 and a non-clustered IX on column 23. A table with a clustered IX on column 1 and a non-clustered IX on column 2 including column 3Finally I run a simple update statement while outputting STATISTICS IO. The update statement updates column 3.[u][b]I am expecting to see the following:[/b][/u][u]Update on table 1[/u]A clustered index scan followed by a clustered index update.[u]Update on table 2[/u]A non-clustered index scan followed by a clustered index update. There are fewer pages in the non-clustered index and the update should not affect the non-clustered index in any way. I am expecting to see fewer reads for this operation.[u]Update on table 3[/u]A non-clustered index scan followed by a clustered index update and a non-clustered update. There are fewer pages in the non-clustered index and the update will also affect the non-clustered index. I am expecting to see somewhere in the region of double the reads for this operation.[u][b]I actually saw the following:[/b][/u][u]Update on table 1[/u]A Clustered index scan - 22 PagesScan count 1, logical reads 24elapsed time = 29 ms.[u]Update on table 2[/u]A non-clustered index scan - 18 pagesScan count 1, logical reads 10341elapsed time = 117 ms.Unexpectedly, logical reads have increased massively[u]Update on table 3[/u]A non-clustered index scan - 20 pagesScan count 1, logical reads 30343elapsed time = 5201 ms.Now logical reads have increased three times over the second update.Here is my test code:[code="sql"]-- Make sure tables don't existIF EXISTS(SELECT * FROM sys.tables WHERE name = 'ONLY_PK')BEGIN DROP TABLE ONLY_PK;END;IF EXISTS(SELECT * FROM sys.tables WHERE name = 'NO_INCLUDE')BEGIN DROP TABLE NO_INCLUDE;END;IF EXISTS(SELECT * FROM sys.tables WHERE name = 'ONE_INCLUDE')BEGIN DROP TABLE ONE_INCLUDE;END;-- Create Test Tables-- One table to contain only a PKCREATE TABLE ONLY_PK (PK_COL INT NOT NULL PRIMARY KEY, IX_COL INT NOT NULL, INC_COL INT NOT NULL);GO-- One table to contain a PK and an NC index with no included columnCREATE TABLE NO_INCLUDE (PK_COL INT NOT NULL PRIMARY KEY, IX_COL INT NOT NULL, INC_COL INT NOT NULL);GOCREATE NONCLUSTERED INDEX IX_NO_INCLUDE ON NO_INCLUDE (IX_COL);GO-- One table to contain a PK and an NC index containing an included columnCREATE TABLE ONE_INCLUDE (PK_COL INT NOT NULL PRIMARY KEY, IX_COL INT NOT NULL, INC_COL INT NOT NULL);GOCREATE NONCLUSTERED INDEX IX_ONE_INCLUDE ON ONE_INCLUDE (IX_COL) INCLUDE (INC_COL);GO-- Load dummy data into our three test tablesWITH CTE AS ( SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY a.object_id) AS RowNo FROM sys.all_columns a CROSS JOIN sys.all_columns b)INSERT INTO ONLY_PKSELECT RowNo, RowNo, RowNoFROM CTE;INSERT INTO NO_INCLUDESELECT * FROM ONLY_PK;INSERT INTO ONE_INCLUDESELECT * FROM ONLY_PK;GO-- Have a look at the index page countSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('ONLY_PK'), NULL, NULL, 'DETAILED'); -- 22 pages for clustered indexSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('NO_INCLUDE'), NULL, NULL, 'DETAILED'); -- 18 pages for nonclustered indexSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('ONE_INCLUDE'), NULL, NULL, 'DETAILED'); -- 20 pages for nonclustered index-- Switch on stats for the update testSET STATISTICS IO ON;SET STATISTICS TIME ON;SET STATISTICS XML ON;UPDATE ONLY_PKSET INC_COL = 0;-- Clustered index scan - 22 Pages-- Scan count 1, logical reads 24-- elapsed time = 29 ms.UPDATE NO_INCLUDESET INC_COL = 0;-- Non clustered index scan - 18 pages-- Scan count 1, logical reads 10341-- elapsed time = 117 ms.UPDATE ONE_INCLUDESET INC_COL = 0;-- Non clustered index scan - 20 pages-- Scan count 1, logical reads 30343-- elapsed time = 5201 ms.SET STATISTICS IO OFF;SET STATISTICS TIME OFF;-- This code will reset the tables.UPDATE ONLY_PKSET INC_COL = PK_COL;UPDATE NO_INCLUDESET INC_COL = PK_COL;UPDATE ONE_INCLUDESET INC_COL = PK_COL;[/code]Fri, 17 May 2013 03:12:41 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1454800-2799-1.aspxHello, we are using SQL Server 2012: select @@version; --Microsoft SQL Server 2012 - 11.0.2100.60 (X64) --Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)and our server over the past few weeks has seen the optimizer memory grow from only a few tens of MB to 20GB, 30GB, 40GB, and beyond. It is completely hogging the memory on the server. Our page life expectancy has decreased significantly since more than half of the 60GB of memory available is going to the optimizer. There seems to be no way to stop the optimizer memory from increasing. Our workload is pretty ad hoc, so I enabled optimize for ad hoc workloads, but this did not slow it down. We tested clearing the caches, but it appears optimizer memory is not the plan cache, so clearing them did not help, in fact it may have made it worse since it freed up even more memory for the optimizer to consume. On our healthy servers, this memory is usually less than 1 GB so having it grow to 40GB and higher is surprising to me. Here's some relevant stats: total memory for the server is 60GB with 4GB left for the OS. This instance is the only instance of SQL Server and the only software running on the server. No anti-virus is running on it. The flag for process_physical_memory_low is 1, process_virtual_memory_low is 0. The server memory status is "Available physical memory is high". All 60GB are being used. PLE (page life expectancy) ranges from 0 to a few hundred to a thousand, but we are used to 10s or even 100s of thousands. We also collect stats on page reads or hard-faults by taking two snapshots and taking the difference since the counters are cumulative. So page reads /sec is oddly 0 in the past few hours, but I can see as PLE goes down to 0-10, page reads /sec goes way up. Our stolen memory counter is positively correlated with the optimizer memory --- with stolen memory reaching 50GB when the optimizer memory is at 47GB, currently. I can see latch waits /sec is much higher than normal and lots of IO wait stats, probably b/c the disks are being used much more than normal since most memory is going to the optimizer. Any ideas as to why the optimizer would behave like this in SQL Server 2012? Restarting the server will return us to a low optimizer memory and allow data caching as usual, but I am more interested in possible causes of this behavior as I would expect it to come back over a few weeks anyway after a restart. Thanks much!Mon, 20 May 2013 21:15:36 GMTblueazulhttp://www.sqlservercentral.com/Forums/Topic1454189-2799-1.aspxI have sql server 2012 and 2008R2 installed on my local.Prior to 2012 installation, i would just open SSMS from start by typing 'ssms', after 2012 installation if it type 'ssms' it would open management studio for 2012. Seems like 2008R2 and 2012 have same extensions for SSMS, is there way to just open 2008R2 management studio from start?Fri, 17 May 2013 14:39:06 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1454422-2799-1.aspxHi All,I have an interesting integration scenario as follows ...1. An (awful) 3rd part app has it own code to execute backs and restores which the users can execute2. I need to recognise when SQLS a) [u]starts a restore[/u] so I can execute a [i]prep [/i]sproc , and, b) [u]completes a restore[/u] so I can execute [i]finalise [/i]sproc Whats the best way to do this SQL Server 2012?Have I got the best approach? e.g. is a "restore complete" event best or would a "DB Online" event be better?Mon, 20 May 2013 01:50:48 GMTbelgarionhttp://www.sqlservercentral.com/Forums/Topic1454005-2799-1.aspxFrom SSMS Plan: <StmtSimple StatementSubTreeCost="329.661" StatementEstRows="9" StatementOptmLevel="FULL" QueryHash="0x8D96B6E9E59734B3" QueryPlanHash="0xB33E59170E9A855A"><QueryPlan DegreeOfParallelism ="8" MemoryGrant="3263496" CachedPlanSize="280" CompileTime="160" CompileCPU="160" CompileMemory="4248"> From App Plan: <StmtSimple StatementSubTreeCost="6.74389" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x8D96B6E9E59734B3" QueryPlanHash="0x44E50B9406BB5E36" StatementOptmEarlyAbortReason="TimeOut"><QueryPlan DegreeOfParallelism ="1" MemoryGrant="5120" CachedPlanSize="240" CompileTime="100" CompileCPU="100" CompileMemory="4048"> ThanksFri, 17 May 2013 07:31:16 GMTisuckatsqlhttp://www.sqlservercentral.com/Forums/Topic1454025-2799-1.aspxHello,Currently I a am referencing the ReportExecution2005 on my web service application. This web service is use to print report automatically. Currently, we have this application running on sql server 2005. We are testing this application with Sql Server 2012; For some reason, I found two issues, but I have found a solution for one.Font in SSRS 2012 is much bigger on then on 2005. I had two specify DPI 96 on Device Info to print the report. The second issue I am having when the report prints. It only prints one page instead of all the pages. Any thoughts?Fri, 17 May 2013 08:07:00 GMTjosef 34192http://www.sqlservercentral.com/Forums/Topic1453922-2799-1.aspxI have a cluster with 2 database instances. There is a linked server set up from one instance to the other. The database that the linked server connects to is mirrored to another server at a remote site. The instance with the linked server does not know anything about the remote server or mirrored databse. The link is working fine but the remote/mirror instance is constantly reporting that the linked server is attempting to connect to it and failing with a pasword mis-match. I know the failed logins are from the linked server because the IP address is that of the node hosting it and the user is an account specifically set up for the link.The only reason I can come up with is that some failover code in SQL Server is going wrong, working out that the database is mirrored, routing the linked server to the mirror database, failing, then correctly routing it to the primary database.As far as I know the link always works but I worry that it may not perform optimally when connections are going astray like this.Note: I am using clustering and mirroring but not the new "always on" functionality.Anyone have any ideas what is going on here.P.S. I also mirror my ReportServer database and the log shows that Reporting Services is repeatedly attempting to connect to the mirror. Again, everything is working fine but the errors are worrying.Fri, 17 May 2013 04:27:06 GMTPaulRoss@redrowhttp://www.sqlservercentral.com/Forums/Topic1453744-2799-1.aspxOK, so I convinced after reading lots of articles that MDM using SQL Server 2012 is a good thing!! Well done MS marketing machine.... I have found lots of articles talking about concepts and the registry pattern looks the job for me and what I am doing... tick.... Now how do I implement the registry pattern to do distributed queries to several Azure databases from my local server?? I can't find anything that gives me this how-to for this approach....Please can someone point me in the right direction??Steve.Thu, 16 May 2013 12:54:12 GMTSteve Newtonhttp://www.sqlservercentral.com/Forums/Topic1451836-2799-1.aspxSUMMARY:I have many databases with full transaction logs. They are already in SIMPLE recovery model,and I cannot add a new log or truncate the existing one, because the transaction log is full due to 'CHECKPOINT'DETAILS:(Examining just one of the problematic databases)I have a SQL Server 2012 SP1 database with a full transaction log.This database has one data file (750MB) and one log file (850MB)Compatibility level = SQL Server 2012 (110)Recovery Model is SimpleUnder Recovery Options, Page Verify=CHECKSUM and Target Recovery Time=0I had 5G of free disk (which should have been enough), and I added more, so now I have 25G free.My log file growth is set to: "By 10 percent, Limited to 2097152 MB " (I can't change it - if I try I get an error because the transaction log is full)I can't do a backup - get an error because the transaction log is full due to 'CHECKPOINT'I can't add another log file - get an error because the transaction log is full due to 'CHECKPOINT'Verifying that file#2 is my log file[code] SELECT file_id, name FROM sys.database_files; [/code]I can't do a truncate only shrink successfully:[code] DBCC SHRINKFILE (2, TRUNCATEONLY);[/code] - get an error because my log is out of space - with a cascade error that the transaction log is full due to 'CHECKPOINT'DBCC CHECKDB mostly just gives errors because the transaction log is full due to 'CHECKPOINT'I can't change the recovery model to full - get an error because the transaction log is full due to 'CHECKPOINT'I can't change the recovery model to bulk-logged - get an error because the transaction log is full due to 'CHECKPOINT'From sys.databases,is_read_only=0, state=0, state_desc=ONLINE, is_in_standy=0is_cleanly_shutdown=0, is_supplemental_logging_enabled=0is_read_committed_snapshot_on=0recovery_model=3, recovery_model_desc=SIMPLEis_fulltext_enabled=1is_published=0, is_subscribed=0, is_merge_published=0, is_distributed=0, is_sync_with_backup=0is_broker_enabled=1, log_reuse_wait=1, log_reuse_wait_desc=CHECKPOINTI tried doing a manual CHECKPOINT (using the CHECKPOINT cmd in MgmtStudio) and got errors:[quote](1 row(s) affected)Msg 5901, Level 16, State 1, Line 3One or more recovery units belonging to database 'MYDATABASENAME' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.Msg 9002, Level 17, State 1, Line 3The transaction log for database 'MYDATABASENAME' is full due to 'CHECKPOINT'. [/quote][code]SELECT count(*) FROM fn_dblog (NULL, NULL);[/code]4,521,289That seems like a lot !?My ERRORLOGs are full of errors about full transaction logs. I have many databases with this same issue - I've just focused on one, for testing.DBCC OPENTRAN gives me no open transactionsHowever, [code]select * from sys.dm_tran_database_transactions where database_id = MYDBID[/code]gives me 6 rows, of which 4 have null begin timesI tried using[code] select request_session_id, * from sys.dm_tran_locks where resource_database_id = MYDBID and resource_subtype = 'BULKOP_BACKUP_LOG'[/code]and killing those sessions, but they always come back immediatelySat, 11 May 2013 10:21:35 GMTf242337http://www.sqlservercentral.com/Forums/Topic1450050-2799-1.aspxHi,I have installed 3 new instances of SQL Server 2012 on a stand alone server. SQL Service is set to automatic on all of them. Yet, suddenly, all the instances have shut down abruptly throwing the following errors. Need some advice around this issue?SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.During undoing of a logged operation in database 'tempdb', an error occurred at log record ID (438:376:163). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.Tue, 07 May 2013 04:39:25 GMTBenki Chenduhttp://www.sqlservercentral.com/Forums/Topic1453694-2799-1.aspxI am using Visual Studio 2008 version 9.0.30729.1 SP (BIDS) to create a package that will copy data from a SS2000 database table to a SS2012 database table. It works great when I run it in BIDS from my Windows XP machine. When I schedule it to run as a SQL Server Agent job on the SS2012 server, it failed with the following error message.[i]MessageExecuted as user: NT Service\SQLAgent$SQLPROD. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:32:36 AM Error: 2013-05-16 11:32:37.44 Code: 0xC0209303 Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: The requested OLE DB provider SQLNCLI10.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2013-05-16 11:32:37.44 Code: 0xC020F42A Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: Consider changing the PROVIDER in the connection string to SQLNCLI11 or visit http://www.microsoft.com/downloads to find and install support for SQLNCLI10.1. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "sqlprod.AOC_Applications.sa" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:32:36 AM Finished: 11:32:37 AM Elapsed: 0.764 seconds. The package execution failed. The step failed.[/i]After wading through all that text, I focused in on the following: [b]Description: Consider changing the PROVIDER in the connection string to SQLNCLI11[/b]Since my connection string is provided through a configuration file, I went in and changed SQLNCLI10.1 to SQLNCLI11 as suggested. And great news! I got a different error.[i]MessageExecuted as user: NT Service\SQLAgent$SQLPROD. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:30:49 PM Error: 2013-05-16 12:30:50.03 Code: 0xC020801E Source: SendEmail Log provider "{31503914-01FB-4A31-880A-C914E7B2A6E1}" Description: The connection manager "sqlprod.AOC_Applications.sa" is an incorrect type. The type required is "FILE". The type available to the component is "OLEDB". End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0202009 Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Client unable to establish connection". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.". End Error Error: 2013-05-16 12:30:50.06 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "sqlprod.AOC_Applications.sa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:30:49 PM Finished: 12:30:50 PM Elapsed: 0.608 seconds. The package execution failed. The step failed.[/i]Out of which I extracted the following:[b][i]Description: "SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.". [/i][/b]So I googled that helpful tidbit, and found this link: [url=http://www.biadmin.com/2012/06/sql-2012-upgrade-gotcha-sql-2000-not.html][/url]In there, the author said the following: [b][i]All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider.[/i][/b] From this, I'm guessing that I have to use a different SSIS connection manager.So I have some questions. First, is this a surmountable issue? Second, have I diagnosed the fix correctly? And if so, what connection manager type should I use?Thanks, and my apologies if I'm in the wrong forum.MattieThu, 16 May 2013 11:12:00 GMTMattieNHhttp://www.sqlservercentral.com/Forums/Topic1453654-2799-1.aspxI am planning for the update from SQL Server 2008 R2 to SQL Server 2012. I've read some accounts of differences between running Always On and WSFC on the older version of the OS vs. the newer. NB: I *am* trying to get a comment on this directly from Microsoft, but they're not being responsive so far. I am alo taking a Microsoft training course that purports to cover HA, but the references are superficial re this question.The concerns revolve around updates to the database(s) on the servers in an Always On Availability Group. Are there substantial differences between the steps to patch SQL Server 2012 running on multiple machines supporting multiple DB's when it's running on Server 2008 vs. Server 2012? Our primary enterprise application, which would *definitely* be in an Availability Group, gets 4 business critical updates a year. Two of those are often taken under operating season pressures such that I don't want to add complexity that could be avoided. Are there substantial differences between the steps required to install and promulgate a new DB version under different versions of the server OS? Note that, in all cases, the Availability Group will host at least two databases, but that only one of them will require major updates.Does anyone have enough hands on experience with AlWays On and WSFC to venture ideas? Any suggestions on where (official Microsoft?) sites/blogs/????) I might try for an authoritative answer?Thu, 16 May 2013 10:10:28 GMTrray 44280http://www.sqlservercentral.com/Forums/Topic1453591-2799-1.aspxI have a requirement from my customer to enable auditing, few at the server level and few at the database level.I have been successful in enabling the audit.Now, I have a question of we can audit the changes in the auditing configuration itself?For example..If someone deletes a particular setting in the audit properties, I want that to get logged too.Is it possible.Thu, 16 May 2013 08:48:10 GMTBenki Chenduhttp://www.sqlservercentral.com/Forums/Topic1453235-2799-1.aspxSo I have a staging customer table that holds combined customer information for several different databases. All our customer information across different platforms is combined and standardized in this staging table. Based on this data - we normalize it and then either update the customer information in our Fact table if that customer already exists - or we insert a new customer record if that customer doesn't exist. Our current Fact table has 10+ million records in it. Our staging customer table has millions in it as well. I know these numbers aren't exactly big - but I am also planning for the future as well. So - my question is are there any 3rd party tools on the market that can help with the update and the inserts? Or is my only option scripting it all out in either a stored procedure or an SSIS package. I looked at Red gate's SQL compare - but it looks like it works better if you run it ad hoc. I am looking for something to create and then schedule it so it runs automatically. Any suggestions is greatly appreciated.Wed, 15 May 2013 11:27:36 GMTsrobinson 596http://www.sqlservercentral.com/Forums/Topic1453336-2799-1.aspxhi,i ran a job which is updating almost 300 GB database.now i see that the Db is in recovery mode. I know that I should wait till it recovers. [code="sql"]EXEC dbo.sp_stop_job N'jobname'[/code]says the job is not running, but the [code="sql"]SELECT *FROM msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobactivity AS SJA ON SJ.job_id = SJA.job_idWHERE SJ.enabled = 1 AND NOT SJA.start_execution_date IS NULL AND SJA.stop_execution_date IS NULL[/code]gives two records...[code="sql"]DBCC CHECKDB[/code] says, deffered transactions are there.Any help would be highly appreciated...Thanks,Regards,AmiThu, 16 May 2013 00:29:20 GMTAnamikahttp://www.sqlservercentral.com/Forums/Topic1453307-2799-1.aspxCan anyone provide links for hosting of a small sql 2012 (Business Intelligence - Tabular mode) server of 2GB memory, 1GB of HHD space and medium CPU power? Any hosts around the asia-pacific area is recommended, who are the names you can trust these days? Thanks.Wed, 15 May 2013 20:47:33 GMTskeezwizhttp://www.sqlservercentral.com/Forums/Topic1453268-2799-1.aspxOk the following features are having issues when I do the installation (SQL Server 2012 dev on Windows 8 Pro)Reporting Services NativeDatabase Engine ServiceDQSFull Text and Semantic Extraction for SearchSQL Server ReplicationListed the message for database engine below. On the way through the installation there was an error message that said"Wait on database engine recovery handle failed. Check the SQL Server error log for potential causes.Action required:Use the following information to resolve the error, uninstall this feature, and then run the setup process again.Feature failure reason:An error occurred during the setup process of the feature.Error details:§ Error installing SQL Server Database Engine Services Instance FeaturesWait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.Error code: 0x851A001AVisit http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3128.0&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026 to get help on troubleshooting.Any suggestions gratefully received. This is like the hundredth time I have tried to install SQL Server.Oh and I have disabled the firewall.CheersE:sick:Wed, 15 May 2013 14:10:35 GMTEllshttp://www.sqlservercentral.com/Forums/Topic1452407-2799-1.aspxHi all, I have a db that i am restoring from a SQL 2005 server to 2012, which is in the wrong collation (It was wrong for the old server as well). From what I understand if you do a ALTER DATABASE this will only change future data and the current data will still be in the old collation? I found the following KB:[url=http://support.microsoft.com/default.aspx?scid=kb;en-us;325335]http://support.microsoft.com/default.aspx?scid=kb;en-us;325335[/url]Is this still the only way to update everything to a new collation (the kb says its for 2000 and 2005)? or in 2012 can you get away with just an ALTER DB?Hope someone can help. Tue, 14 May 2013 01:02:23 GMTn00bDBA