SQLServerCentral.com / SQL Server 2014 / Administration - SQL Server 2014 InstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 19:05:56 GMT20Failover Clusterhttp://www.sqlservercentral.com/Forums/Topic1707693-3411-1.aspxI have creating fail-over cluster in windows server 2012. During validation, I got some warning as below.I got storage and Validate Storage Spaces Persistent Reservation related warning as in detail below."Persistent Reservation Registration key 0x100b2 for Test Disk 0 reservation holder node MTCBJSVR53.mtrade.com was not removed by the call to REGISTER using RESERVATION KEY 0x100b2 and SERVICE ACTION RESERVATION KEY 0x100c2."Persistent Reservation Registration key 0x3000100b2 for Test Disk 3 reservation holder node MTCBJSVR53.mtrade.com was not removed by the call to REGISTER using RESERVATION KEY 0x3000100b2 and SERVICE ACTION RESERVATION KEY 0x3000100c2.Test Disk 0 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage Spaces.Test Disk 1 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage Spaces.Test Disk 2 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage Spaces.Test Disk 3 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage Spaces.Test Disk 4 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage Spaces.Test Disk 5 does not support SCSI-3 Persistent Reservations commands needed by clustered storage pools that use the Storage Spaces subsystem. Some storage devices require specific firmware versions or settings to function properly with failover clusters. Contact your storage administrator or storage vendor for help with configuring the storage to function properly with failover clusters that use Storage SpacesSAN is fiber I am setting up cluster from production. Could you please help that whether it is serious warning or i can simply ignore it.Sat, 01 Aug 2015 03:32:06 GMTkeshab.basnetWindows and SQL failover cluster setuphttp://www.sqlservercentral.com/Forums/Topic1707681-3411-1.aspxI am going to set up fail-over cluster. Could some one please give me answer of following. During the windows failover cluster set up, we need to be logged in by local administrator log in of Each cluster Node or by domain admin user log to install following components by add Roll and Features via Server manager?1. .net Framework features2. Fail over clustering in each cluster Node3. File and IScsi Services4. Storage ServicesAlso to install SQL server Failover cluster setup, We need to be logged in by local administrator login of each cluster node or Domain admin login to install sql failover cluster?Thanks in advance i am expecting answers as soon as possible.Fri, 31 Jul 2015 21:20:42 GMTkeshab.basnet18456 error with state 38http://www.sqlservercentral.com/Forums/Topic1707571-3411-1.aspxLogin failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT:xx.xx.xx.xx]No missing database & having sysadmin permission.can u pls give me reason why this error comingFri, 31 Jul 2015 11:13:03 GMTgiri10488Restoring from Commvaulthttp://www.sqlservercentral.com/Forums/Topic1707488-3411-1.aspxHello All,I am just wondering, has anyone used Commvault? If I restore SQL databases from tape using Commvault, will it just dump the MDF's and LDF's to a folder?Should I be able to just take those MDF's and attach them in SQL?FYI - this will be for SharePoint. So I would be attaching (if possible) the configuration database(s) and content database(s).Would this work out? Anyone have any experience with how long the restore from Commvault takes? It is about 6 TB of data.Thank you!Fri, 31 Jul 2015 08:16:53 GMTprofintellectAG in Resolving statehttp://www.sqlservercentral.com/Forums/Topic1707376-3411-1.aspxI have a 2 node cluster on server 2012r2/sql2014. Recent patching (thanks MS) took down one of the nodes hard. The second one won't come out of the Resolving state. Any ideas?Thu, 30 Jul 2015 17:08:41 GMTSQLHeapupgradingSQL 2008 R2 active\active Cluster to SQL 2014 clusterhttp://www.sqlservercentral.com/Forums/Topic1705449-3411-1.aspxHi I have a 2 node cluster in my production environment. The cluster is SQL 2008 R2 enterprise edition and setup as active\active with a maximum of 12 instances on it. I'm planning to upgrade the cluster to SQL 2014 enterprise for all the 12 instances that are running on the cluster. Now I need to know if I can do a straight in place upgrade without having to first upgrade to SQL 2012 and then upgrade to SQL 2014.Fri, 24 Jul 2015 03:40:27 GMTFTdenaliAlways On restore questionhttp://www.sqlservercentral.com/Forums/Topic1707275-3411-1.aspxWe are performing a SharePoint upgrade in our environment which consists of 60 databases in an AO availably group. Generally when we perform software upgrades we snapshot the software servers and backup all databases and if the upgrade fails we have restore the snapshots then restore the database. The database restore alone can take hours. Can this be done another way? My always on avail replicas are Svr1 and Svr2 with Svr1 as the primary. If I shutdown Svr2 when I snapshot my SharePoint servers and the upgrade fails would I be able to simply revert the SharePoint snapshots, shutdown Svr1 and bring up Svr2 as the primary to get the SharePoint site back up? Then proceed with removing the db's from the avail group on Svr2, bring up Svr1, drop the dbs on Svr1 then re-add them to the avail group on Svr2.I'm using SharePoint in this hypothetical, but what I'm really asking is can a secondary server replace a restore if necessary, in other words, is this a feasible restore plan? And of course I would backup my databases prior to the upgrade regardless.Thu, 30 Jul 2015 11:40:34 GMTSDG1Restore Database permission failurehttp://www.sqlservercentral.com/Forums/Topic1706807-3411-1.aspxI would appreciate any help someone can give me with this.I'm trying to figure out why this SQL Agent job keeps failing. We used management studio, and connected as mydomain\myuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.This is the error message we got:[b]"Executed as user: mydomain\myuser. Create Database permission denied in database master'.[/b]So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to: [b]"Executed as user: mydomain\myuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. " [/b]* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account.* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles* The user is a member of dbowner on the database I am trying to overwrite with the restore* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASEThe only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the Server\InstanceName is MyServer-New[url]http://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/[/url][code="sql"]SELECT HOST_NAME() AS 'host_name()',@@servername AS 'ServerName\InstanceName',SERVERPROPERTY('servername') AS 'ServerName',SERVERPROPERTY('machinename') AS 'Windows_Name',SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',SERVERPROPERTY('instanceName') AS 'InstanceName',SERVERPROPERTY('IsClustered') AS 'IsClustered'[/code]I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.Has anyone seen anything like this before? Am I on the right track with the name change messing up permissions, or is there something else I need to check?Thanks for any help anyone can offerWayneWed, 29 Jul 2015 11:56:59 GMTwayne.mcdanielcrystal report cannot select into temp tablehttp://www.sqlservercentral.com/Forums/Topic1707280-3411-1.aspxI know select * into ##tmp1 should work but I get a database connection error.Thu, 30 Jul 2015 11:56:00 GMTmfriessnigMonitor AG Listener Remotelyhttp://www.sqlservercentral.com/Forums/Topic1706526-3411-1.aspxHi Guys,I need to check connectivity to AG listener remotely and log that to a table/file. What is the best way to do that? Powershell?Tue, 28 Jul 2015 19:25:46 GMTsalamlemonQuery performance extremely poorhttp://www.sqlservercentral.com/Forums/Topic1706191-3411-1.aspxHi allI'm experiencing a performance issue on the whole instance. Queries used to execute very well recently and in the last couple of weeks they perform poor.A SP that was executing in just a second, now executes in minutes.Update of the statistics of tables is done very frequently. When I run sp_updatestats for the database it says that there is no need of updateing the stats...This is very strange ...Tue, 28 Jul 2015 01:46:24 GMTIgor MicevOne Node could not up in SQL Server Failover clusterhttp://www.sqlservercentral.com/Forums/Topic1705069-3411-1.aspxWe have 2 nodes window Server 2012 R2 and SQL Server 2012 Enterprise Version cluster setup. We can switch roles and Node to one node to another and revert back to previous node with out any issues. But we are facing when one Node is restarted. We could not restart that Node in cluster Service start in Failover cluster Manager. Error Details is displayed as below inside double code."Cluster node NODE1 could not to join the cluster because it failed to communicate over the network with any other node in the cluster. Verify the network connectivity and configuration of any network firewalls."I checked windows firewall. windows firewall is all of in Node1, Node2, SAN and DC. I have disabled and enabled the Internal and private network of Node 1. I have validated the cluster. it is showing no error though.Node1:Public IP: 10.10.0.11SubNet Mask:255.255.255.0Default Getway: 10.10.0.1Prefered DNS: 10.10.0.10 (Ip of DNS)Private: 10.10.0.5Subnet Mask: 255.0.0.0Node2:Public IP: 10.10.0.12SubNet Mask:255.255.255.0Default Getway: 10.10.0.1Prefered DNS: 10.10.0.10 (Ip of DNS)Private NetworkIP: 10.10.0.6Subnet Mask: 255.0.0.0SAN:Public IP: 10.10.0.13SubNet Mask:255.255.255.0Default Getway: 10.10.0.1Prefered DNS: 10.10.0.10 (IP of DNS)Private Network: Not configured.pinging to each other ip is successful from one node to another. Could you please suggest how to solve this issue. Thu, 23 Jul 2015 06:39:38 GMTkeshab.basnetSQL AG - Listenerhttp://www.sqlservercentral.com/Forums/Topic1706239-3411-1.aspxHello I have built a SQL 2014 Always on Cluster. I need to create three Availability groups and listeners in the same instance. Do I need 3 separate IP address for each listener or one IP address can be shared for all 3 Listeners ?sqlrobTue, 28 Jul 2015 05:16:42 GMTsqlrobwhat does restore log do after the log is restored?http://www.sqlservercentral.com/Forums/Topic1706963-3411-1.aspxWhen you see the title, don't get me wrong:)I am not sure if anyone see when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.What does SQL Server or Windows do after the restoring?pic a:[img]https://social.msdn.microsoft.com/Forums/getfile/700456[/img]pic b:[img]https://social.msdn.microsoft.com/Forums/getfile/700457[/img]I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .pic 1:[img]http://i58.tinypic.com/23moj9z.png[/img]pic 2:[img]http://i58.tinypic.com/2aj5lx1.png[/img]please see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...Wed, 29 Jul 2015 17:23:49 GMTi1888SQL Server Change Data Capturehttp://www.sqlservercentral.com/Forums/Topic1706465-3411-1.aspxWhen using Change Data Capture on SQL Server 2012 I have researched that you cannot truncate data in a table. Is this also true if one wanted to delete data from the table? Getting a little confused about what DDL statements can be ran against a table with CDC enabled. Does CDC have to be disabled before performing certain DDL statements against a table? I would like to safeguard the truncation and dropping of certain tables within the dbo schema. Wondering if I could do this with one fail swoop with CDC enabled on those tables. The other option would be to use a DDL trigger to prevent certain DDL statements to be performed.Any further documentation or threads would be greatly appreciated. Continuing to research the behavior of CDC in the interim.Thanks in advance. Regards,JonathanTue, 28 Jul 2015 13:57:32 GMTJonathan MarshallAll Client Machine details since the SQL SERVER Installationhttp://www.sqlservercentral.com/Forums/Topic1707007-3411-1.aspxHi there:Trust that everyone in the group are in high spirits :-DHave a simple question for you all. ;-)Is there any possibility of getting all those Client Machine Details connected to the SQL SERVER Database Server Machine since the Database has been Installed?With Kind Regards,DEXTER:w00t:Thu, 30 Jul 2015 00:10:49 GMTdextInvestigating Slow Restore Processhttp://www.sqlservercentral.com/Forums/Topic1705850-3411-1.aspxI have a SQL Server 2014 instance. I am restoring native backups that reside on a share on a virtual machine over to a physical machine. The size of the back up is about 130 GB and the database is in the neighborhood of 700 GB after it has been restored. For some reason it is taking almost a full 24 hours to do a restore. The two machines are on the same network and subnet. I can also tell you that just transferring files between these two machines is extremely slow and most file transfers between any machines on the network are slow. I'm concerned for a number of reasons, but mainly because this issue will hurt our ability to recover quickly, and currently, it makes it painful to refresh non-prod from a prod backup. I don't have a Windows or Network admin at my job and I am hoping I can get some help here. What do I need to check or what things do I need to consider that might be causing this issue?Sun, 26 Jul 2015 18:34:42 GMTlmarkumSelecting a date range month over month (reporting related question)..newb question :(http://www.sqlservercentral.com/Forums/Topic1706781-3411-1.aspxSo basically, I'd like to get a count of a few different fields within a table, BUT ONLY for a specific date range month over month. In other words, I want the 1st of every month through the 23rd of every month.SELECTDATE_TRUNC('month', n.campaign_date) &lt;--- i want the date trunc to reflect ONLY the 1st - 23rd of every month. I then want to get the count of various metrics in the same table assocaited with the 1-23rd of every month. I'm basically doing reporting month vs month to gauge effectiveness.^^ This ideally would look like: Date Field Metric Metric2 Metric 3 2015-01-01 - 2015-01-23 45 523 234232015-02-01 - 2015-02-23 45 523 234232015-03-01 - 2015-03-23 45 523 23423,COUNT(numberofresponders),COUNT(othermetric),COUNT(othermetric2)FROMtableWHEREcampaign_date &gt;= '2015-01-01Wed, 29 Jul 2015 10:59:40 GMTdandenise316Finding installed MS sql 2014 is licensed or Trail versionhttp://www.sqlservercentral.com/Forums/Topic1706774-3411-1.aspxCould some please say give me information on following 2 questions.1. How to find out already installed MS sql server 2014 is trail version or Licensed version?2. How to keep license in already installed MS sql server 2014 trail version.Wed, 29 Jul 2015 10:40:44 GMTkeshab.basnetAlwaysOn transaction log filling uphttp://www.sqlservercentral.com/Forums/Topic1704050-3411-1.aspxI have a database that is part of AlwaysOn that is filling up the transaction log drive even though I have a daily full backup and transaction logs set for every 2 hours. The backups are going from both the primary and secondary replica backuping up to the shared disk and I have the backup preferences set to the primary. When I try to shrink the log I get 'The transaction log for database 'DB' is full due to 'LOG_BACKUP''. I have to manually backup the trans log and then shrink, I have no idea why the maintenance plan backups aren't doing this even though they are "working".Sat, 18 Jul 2015 11:08:15 GMTchrisphQDS_SHUTDOWN_QUEUE Wait type SQL Server 2014http://www.sqlservercentral.com/Forums/Topic1705170-3411-1.aspxDoes anyone know what QDS_SHUTDOWN_QUEUE Wait type is in SQL Server 2014? All I can find it is related to query store package and that it can be ignored by it is the top wait on this server. The only thing running on this server is a job that restores 196 full/diff backups, performs DBCC CHECKDB then drops the database.The job is now taking twice as long as it was taking last week. I've disabled the DBCC CHECK step which appears to be what's causing it to take longer and noted some improvement but I'm curious about this particular wait and DBCC CHECKDB. Does anybody know?WaitType QDS_SHUTDOWN_QUEUEWait_Sec 44402.37Resource_Sec 44401.28Signal_Sec 1.1Wait Count 741Wait Percentage 64.85AvgWait_Sec 59.9222AvgRes_Sec 59.9208AvgSig_Sec 0.0015Thu, 23 Jul 2015 09:16:38 GMTymbaezSQL 2014 Availability group amazon Ec2 instance error 35250 join database to availability grouphttp://www.sqlservercentral.com/Forums/Topic1706173-3411-1.aspxHI,I am working on configuring SQL 2014 alwayson and encountering this error "SQL AlwaysOn: Failed to join the database to the Availability Group (Error 35250)" andfollowed all steps according to this msdn link. and matched with last error- DNS error. SO now i would like to know how to resolve this issue as network team is saying that there is no issue at DNS end.http://blogs.msdn.com/b/alwaysonpro/archive/2013/12/09/trouble-shoot-error.aspxWhen i run the query:select r.replica_server_name, r.endpoint_url,rs.connected_state_desc, rs.last_connect_error_description, rs.last_connect_error_number, rs.last_connect_error_timestamp from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas ron rs.replica_id=r.replica_idwhere rs.is_local=1the last_connect_error_description is returned as "Connection attempt failed with error: '10060(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.)'. "and last_connect_error_number=10060connected_state_desc='disconnected'Please help.Mon, 27 Jul 2015 21:21:21 GMTmuthyala_51SQL Agent job package configuration environment user specific?http://www.sqlservercentral.com/Forums/Topic1705900-3411-1.aspxI have an SSIS solution deployed to the SSISDB catalog. It has a number of project parameters which I have setup an environment to provide values to. I then created a SQL Agent job step to run the solution making use of the environment. When a colleague goes to look at the job step they get an error stating there is no value for a parameter, go to the configurations tab and the link to the environment is not there. They then put it in and I get the error.So I assume the environment configuration in the SQL Agent Job step is linked to a user? If so how do we get around the above scenario? ThanksMon, 27 Jul 2015 03:44:17 GMTmandy-570563Need in Alwayson query which would tell me failover timehttp://www.sqlservercentral.com/Forums/Topic1705917-3411-1.aspxHi All,Is there any single TSQL query which provides below info.When did my AlwaysOn Availability group failed over and from which node it failed to which new node(i.e. replica)?Thanks in Advance.Mon, 27 Jul 2015 04:54:06 GMTvsamantha35ASPState DB Upgradehttp://www.sqlservercentral.com/Forums/Topic1706238-3411-1.aspxHi All,We have a db server that needs to be upgraded from sql 2005 to sql 2014. I see ASPSTATE db which is the only one db in this server/instance. Are you guys aware of this db and the best practices on upgrading this db. Do we need a separate instance for aspstate db or can we consolidated on an existing instance. Does it matter if the server collation is different. Please share your knowledge on this. Thanks a lot in advance.Tue, 28 Jul 2015 05:10:39 GMTSQL!$@w$0MEError Upgrading from SQL Server 2008R2 to SQL Server 2014http://www.sqlservercentral.com/Forums/Topic1705918-3411-1.aspxI am having trouble upgrading one of our SQL instances to 2014. The error I am getting is as follows:TITLE: SQL Server Setup failure.------------------------------SQL Server Setup has encountered the following error:Unable to return summary information property 'PID_REVNUMBER' as a string since the property cannot be converted into a string form.Error code 0x84B20001.I can't seem to find a single thread on this particular error related to the installer. I would be grateful for any suggestions.Thanks,RyanMon, 27 Jul 2015 05:00:11 GMTbvlichInstallation of only SSMS with licensehttp://www.sqlservercentral.com/Forums/Topic1705865-3411-1.aspxHi All,App team has purchased license for SQL Server 2014 BI edition and we installed all the features in one of the server.One of the app user wants to install ONLY SSMS in his lap. Can we run the setup with the above licensed product key and install SSMS ?Will it breach any license conditions ?Thank,San Sun, 26 Jul 2015 23:52:34 GMTJoy Smith SanRebuild Indexes on Listenerhttp://www.sqlservercentral.com/Forums/Topic1705992-3411-1.aspxHi,Do I need to rebuild my indexes on my High Availability listeners?When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?Mon, 27 Jul 2015 08:03:45 GMTkrypto69Unable to Shrink log on simple recovery mode databasehttp://www.sqlservercentral.com/Forums/Topic1706112-3411-1.aspxI have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released. Database size: 40,245.13 MB DatafileMB: 38,063.63, DataAvailableMB: 37,085.15 LogfileMB: 2181.51 Sysfiles shows:[code="plain"]fileid groupid size maxsize growth status perf name1 1 4872144 -1 128 2 0 SomeDB2 0 279233 268435456 10 1048642 0 SomeDB_log[/code]The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)I tried running a checkpoint, but that did not allow any resize or shrink to work. I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.The following is what I was using for the transactions to get the log used.[code="plain"]BEGIN TRANselect a.* into testtable from sysobjects a, sysobjects b, sysobjects cROLLBACK TRAN[/code]Each insert creates 93,576,664 rows.Running dbcc SQLPerf(logspace) :[code="plain"]DB LogSize(MB) LogSpaceUsed(%) StatusSomeDB 2181.5 34.20489 0[/code]Running dbcc loginfo:[code="plain"]RecoveryUnitId FileId FileSize StartOffset FSeqNo Status Parity CreateLSN0 2 1143734272 8192 60 2 64 00 2 1143734272 1143742464 57 0 128 0[/code]Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (please tell me there is, I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.Mon, 27 Jul 2015 13:33:42 GMThahnorlaskaSystem objects could not be updated in database 'x' because it is read-only.http://www.sqlservercentral.com/Forums/Topic1706107-3411-1.aspxHi,We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs. I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically. Error Log Entry:System objects could not be updated in database 'x' because it is read-only.Problem:After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated. Solution:Simply set the db to read-write and the system objects will get updated, long after SP1 was installed. ALTER DATABASE [x] SET READ_WRITE WITH NO_WAITThen set it back to read-only:ALTER DATABASE [x] SET READ_ONLY WITH NO_WAITYou should then see these log entries:System objects could not be updated in database 'x' because it is read-only.Setting database option READ_WRITE to ON for database 'x'.Starting up database 'x'.CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.Synchronize Database 'x' (129) with Resource Database.Setting database option READ_ONLY to ON for database 'x'.Starting up database 'x'.CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.Mon, 27 Jul 2015 13:17:34 GMTKGERBRPrimary filegroup for system objects, secondary for data)http://www.sqlservercentral.com/Forums/Topic1705875-3411-1.aspxI have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.I've spent the last two hours googling, and found plenty of references stating this arrangement is "Best Practice", but very little to back up this statement.Can anyone point me in the direction of convincing documentation giving reasons why this is (or is not) the case?Secondly, would anyone care to argue the benefits and drawbacks of this structure. Am I simply making work for myself and complicating the lives of the other team members? Mon, 27 Jul 2015 01:27:28 GMTOtto SchreibkeQuestion on Replicationhttp://www.sqlservercentral.com/Forums/Topic1704804-3411-1.aspxHi All,I am little confused with the below statement. Regarding Subscriber version which depends on the type of publication.As per msdn documentation, i am interested in Transactional replication. So, it says: A Subscriber to a transactional publication can be any version within two versions of the Publisher version. What does it mean?Reference link: https://msdn.microsoft.com/en-us/library/ms143699%28v=sql.120%29.aspxTo be more specific, consider below cases and expecting answers for below:case 1: if Publisher is on SQL 2005, what all SQL versions can I have as subscribers?case 2: if Publisher is on SQL 2008/2008 R2, what all SQL versions can I have as subscribers?case 3: if Publisher is on SQL 2012, what all SQL versions can I have as subscribers?case 4: if Publisher is on SQL 2014, what all SQL versions can I have as subscribers?Thanks in advance.Wed, 22 Jul 2015 08:49:54 GMTvsamantha35Installing failover cluster instance side by side to a standalone installation with alwaysonhttp://www.sqlservercentral.com/Forums/Topic1705107-3411-1.aspxHello All,would like to know if it's possible to install SQL Server Failover cluster instance with shared storage along with standalone installation with Always on ( always on uses WSFC underneath).any link will be great. I want to add a FCI on one of the replica of always on. once I read some an article about it but cant seems to find it.Any help is appreciated.Thu, 23 Jul 2015 08:05:16 GMTqur7Cannot connect to named instance (2nd instance) from local SSMShttp://www.sqlservercentral.com/Forums/Topic1704923-3411-1.aspxHello All,I've two instances(Default, Named[dynamics\FINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamics\FINANCE) using SQL authentication from local SSMS, I get below error message:[b]A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)[/b]I assigned a static port number to the named instance [dynamics\FINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.What am I missing here? Any help is greatly appreciated.ThanksWed, 22 Jul 2015 14:30:58 GMTSSRS NewbieApplication connecting to AG Listener static IP address - not namehttp://www.sqlservercentral.com/Forums/Topic1704855-3411-1.aspxWe have a bought in application written in Visual Basic 6 using ADO 2.7 to connect to SQL Server. The application has two connections strings and uses internal logic to determine which of the two connection strings connects to a read-write database. It does something as simple as trying an update that makes no changes to a very small table somewhere.Previously this application connected to a Mirrored Database hosted on two Windows Server Failover Clusters and SQL Server 2005.For the future the App will be connecting this application to a SQL Server 2014 Availability Group.The Availability Group is made up of four servers, in a cluster, two at one site on Subnet A and the other two at a second site on Subnet B. The Availability Group has been given a Listener with a DNS name of AGLISTEST on port 1433 with two Static IP addresses, one for each subnet.Our Contractor has configured the Application’s connection strings to connect to the two Static IP addresses assigned to the AG. He is of the opinion that although ADO has zero understanding of an Availability Group, at least one of the Static IP Address will translate to the Availability Group primary server because it is a Cluster Resource and so act like a Cluster IP Address on Windows Server Failover Cluster; i.e. sort of mimicking the previous configuration. This does work and he has demonstrated it, however I can’t find any article that proves to my satisfaction this is intended behavior not something that accidentally works. If it is accidental behavior then that would be something that might stop working at an unexpected point in the future.All the research I have found relates to connecting to the AG Listener name, and all the multi-subnet issues and Active Directory issues that go with connecting using what is a legacy client.So; the question is, is this translation / redirection of static IP Addresses assigned to an Availability Group listener documented and safe?Wed, 22 Jul 2015 11:43:34 GMTian.procterSQL Server 2014 EE Features for a DWhttp://www.sqlservercentral.com/Forums/Topic1705221-3411-1.aspxWe are in the process of installing SQL Server 2014 EE for a Datawarehouse. Just wondering what features would you recommend that we install? For example, Database Engine Services, Analysis Services, or any Shared Features, etc.Thanks in advance.Thu, 23 Jul 2015 11:19:38 GMTHookSqlDba7WIndows server 2012 R2 Failover Cluster and SQL Server 2012 Failover clusterhttp://www.sqlservercentral.com/Forums/Topic1704776-3411-1.aspxI am going to configure HA - Active - Active Failover cluster using 2 Nodes. I have already installed Windows Failover cluster. Before SQL server installation, We need to configure MSTDC Right? I have 2 MSDTC SAN drives.Could you please suggest me how to configure MSDTC before SQL Server 2012 Failover cluster installation. &lt;&lt;I need to create HA- Active active 2 node cluster. I.e. I need to have 2 instance in 2 node. &gt;&gt;Wed, 22 Jul 2015 07:46:38 GMTkeshab.basnetIndexes with more writes than readshttp://www.sqlservercentral.com/Forums/Topic1703963-3411-1.aspxI have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point. Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes? In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads. I would appreciation suggestions or things I should consider.Fri, 17 Jul 2015 14:29:13 GMTlmarkumError installing SQL Server Database Engine Services Instance Features ohttp://www.sqlservercentral.com/Forums/Topic1704667-3411-1.aspxHello SSC community,(now i see that i failed the headline, it should be:)[b]SQL Server 2014 Installation failed with failures/The System cannot find the specific Path[/b]since 3 days i setup my developement maschine with a fresh win7/64 SP1 with SQL Server 2014 Enterprise When i had done the (felt 5000) necessary MS updates and mashine restarts) after 2 days, i started the MS SQL Server Setup After passing the System Check without errors, the setup run and ends with failuresit allways ends like this: Feature: Database Engine Services Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: [b]0x80004005[/b] Error description: [b]The System cannot find the specific Path [/b] (Translation from German: "Das System kann den angegebenen Pfad nicht finden")Complete with failures: [url=http://joxi.ru/Q2Kpg71s1gePAj]screenshot[/url]The database engine wasn’t even able to start when i tried manually (the SQL service sqlservr.exe timed out)The summary.txt says:[i]Next step for SQLEngine: Uninstall this feature, and then run the setup process again.[/i]I did, but the result was the same.It sounds crazy, but the setup of SQL Server 2008, 2012 and 2014 worked perfect with my old messy windows 7 (before setup a clean system)Do you guys know what the problem is?Wed, 22 Jul 2015 01:24:02 GMTSimpson 64488Migrate DTS package(SQL 2000) to SQL 2014http://www.sqlservercentral.com/Forums/Topic1704867-3411-1.aspxWe need to migrate a SQL 2000 db environment to SQL 2014. As part of the migration there are a lot of DTS packages that needs to be migrated. How/What are the ways I can accomplish this. Please share your experience/thoughts on this. Any help much appreciated as I haven't worked with DTS packages before.Many thanks.Wed, 22 Jul 2015 12:21:24 GMTSQL!$@w$0ME