Tag Info

New answers tagged

1

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is? It is related to Full text catalog. You can find out more info using sys.dm_fts_outstanding_batches e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches ...


2

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID(). You are currently inserting around 4,500 rows into the table per day. The table has around 6.3 million rows at present. That's around 0.7% of the table per day. Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day ...


-3

"…is there any benefit to changing the GUID generation to sequential using newsequentialid()?" No. Sequential GUIDs are only appropriate when there is a clustered index on the GUID column and you want to avoid page splits caused by inserts. Edit to address the comment below: All nonclustered indexes suffer from page splits when data is inserted. For ...


0

Thanks for the ideas, my issue stemmed from residue from previous installation trys. The clue was in the setup logs found in C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\ I went ahead and found all traces in the registry, programs & features, and program folders and eliminated them.


3

I believe your question is, "Is the hotfix in https://support.microsoft.com/kb/2965069 included in Service Pack 2?" The answer as of July 28, 2014 is no. Watch that KB article to see when it changes - I use http://www.WatchThatPage.com for stuff like this.


1

as this is standard SharePoint maintenance the OP is unlikely to be responsible for the code or be able to interfere in it. What they could influence, is the time it is run at. dbo.proc_MSS_DefragGathererIndexes should only really be run manually and after your first crawl but not generally otherwise. Can you confirm this proc has not been automated as ...


0

Probably by design. Obfuscation of the highest level of username makes sense to me... security through obscurity.


2

Following information was found for victim process from the deadlock graph Isolationlevel="read uncommitted (1)" for process that was killed as part of deadlock. lasttranstarted="2014-07-25T00:00:41.230" lockMode="X" procname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_GetNextCrawlBatch" Problematic statement ...


0

Yes it is possible to achieve what you are asking with sql server database replication. The target server must be the same version or higher than the source. Replication can be run at regular intervals or continuously if you need updates "up to the minute".


0

I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server. Let me tell you transaction log shipping is nothing but trasfer of log records from one server to another its quite light and you can create your own 'logshipping' by creating backup,copy and restore jobs. What you are trying to do is one way possible may ...


2

When I run a query with a bad plan the server slow down and all other query, That says they're contending for the same resource, and non problems on disk I/O doesn't say much about why the disk wouldn't be the resource they're contending for. Logically we know that a query with a "bad plan" is going to read a lot of data from the disk, perhaps ...


1

The Log you posted seems incomplete, also it is not the log for SQL Server installation but for patch update. You can see in the log Requested action: Patch Anyways log is helpful because it shows below Product features discovered: Product Instance Instance ID Feature ...


0

You haven't installed SQL Server. You have only installed SSMS. I have checked if Microsoft SQL Server 2012 is in the Programs and Features list. This means that any SQL Server feature is installed. SSMS is one such feature. Exception's description is :SqlException was unhandled by user code.An exception of type 'System.Data.SqlClient.SqlException' ...


1

Found the answer: http://blogs.msdn.com/b/sql_shep/archive/2012/06/21/sql-server-2012-license-core-limitaion.aspx My consultants installed the wrong version (CAL instead of core), so it's limited to 20 cores.


0

From Page Compression Implementation: However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The ...


1

One important thing with shrink operation when Shrinking through management studio is the option 'Release unused space' what this does is ''Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to ...


0

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture. You are basically capturing every query that comes across the server and telling it to wait ...


2

The rule is not to never shrink. The rule is to not shrink when you will need to grow again. Go ahead and shrink to reclaim the space. Deal with the resulting fragmentation. Be aware that shrinking can cause extreme disk load. These are pretty much the only problems resulting from shrinking. Or, rebuild all indexes into a fresh filegroup and drop/truncate ...


2

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case? Yes. Dont touch that shrink button - Its a general rule and a very very sound advice from many renowned SQL Server Gurus - Paul Randal, Brent Ozar, Mike Walsh, Gail Shaw and many more. The database is still in use, and still somewhat growing, but I do ...


0

When you select the "Device" (file) to restore your database from, the Database combobox below is populated and the first item is selected. But! Whatever event that populates the Files grid is not firing. If you explicitly select your database from that drop down, the Files grid will then populate and the "Relocate all files to folder" checkbox will enable. ...


1

Workload groups are a concept of the resource governor feature in SQL server. You would define workload groups (such as a prod group and a test group) on a server and then define the amount of resources that can be used by each. So for instance if you only had one SQL server for test and prod say, you could define the 2 workload groups and limit the ...


0

Are you sure of your version of Sharepoint? I thought SP2010 could not run on SQLS2012 but 2008 and 2008R2. With which machine do you try to access the report server ? If you have SP and SSRS native mode installed on the same machine you will get error due to the address (SP and Report Server use the same). You can change the report server address in the ...


0

There is no direct DMV which will tell you I/O for specific table. I mostly use sys.dm_exec_query_stats DMV which has useful information about query which is taking high Physical and logical reads. Looking at query you can see tables involved if query has multiple table it wont give accurate information but you would know what all tables generate most I/O ...


3

The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations. SELECT d.name as [Database], OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM ...


0

WITH (NOLOCK) is the equivalent of using READ UNCOMMITTED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In any application with high transaction ...


1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint. You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend. if you want dirty reads on some tables only, then NOLOCK hint will help you. SQL server 2005 and up allows you ...


0

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script. Summary of solution: SQL Job with one Job Step The Job Step executes the SSIS package To run a SQL Job with a custom you must also create: an instance-level Credential a Proxy Account which should be mapped to ...


1

There is not much to check if you have found out that SQL Server requires more memory.Is your OS on physical machine or Virtual if it is virtual adding memory would be easy. You must know that SQL Server 2012 standrd edition supports maximum of 64G memory. After adding new memory you must change max server memory setting using sp_configure. Set max server ...


3

The preemptive_xe_* wait types are associated with Extended Events from what I understand and can find. Considering that and your first sentence: We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. I would start looking at your monitoring tools as the culprit. However being that ...


0

SQL Server works in Non preemtive mode which means that if SQLOS asks it to yeild because it got request from windows OS it will ask SQL server to yeild and SQL Server will listen to it and will yeild or will do as SQLOS has asked it to do. This is because SQL server runs as application and is allocated resources by SQLOS which is monitored by windows O. ...


1

PreEmptive_XXX wait types are those that are: Used to indicate a worker is running code that is not under the SQLOS Scheduling Systems So essentially SQL Server is waiting on external processes to complete before it can continue with it's own processing. It can be caused by the use of CLR or extended stored procedures, so if you have any of those, ...


0

Hi I got solution to this problem. What I did is go to SQL Server configuration manager and go to Instance1 of DR site and remove port 1433 from IPAll. Restart both the instance at DR and now it will point correctly. Please try and let me know. Khaled Bakhtiar.


0

In case have not done that yet, could you please try to perform a repair? Another option could be uninstall SSMS only and install it again. Hope this helps.


1

Please try: • Start regedit.exe • Go to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio • Delete 11.0_Config • Restart ssms.exe There is a similar problem in this feedback to Microsoft, please refer the below link.. ...


0

It seems That this Problem is due to the SSMS versions. What you could do is create the subscription by right clicking on the Publication. I assume you are using SQL 2012 as Distributor/Publisher & 2008 as Subscriber.


0

You can create a stored procedure with execute as owner to only one table or a store procedure to any table. In the next code is SP to truncate any table without assing permission of db_owner or other: USE [database name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Yimy Orley ...


0

What I missed was that the 2008 server required patching. By applying the most recent patch bundle this problem has been resolved.


1

This paper turned in to be very handy for me, but you will still have to do some changes in your script http://download.microsoft.com/download/4/2/6/42616D71-3488-46E2-89F0-E516C10F6576/SQL_Server_to_SQL_Server_PDW_Migration_Guide.pdf


0

An update - I still don't know how best to detect what type of throttling has gone on, but I did find the culprit in my case. It does seem that the CPU CAP to 75% was causing a much higher decrease in performance than a 25% drop should suggest. Raising the CPU Cap to 100% fixed the problem. A connect article seems to validate this might be a problem with ...


2

Yes there is please follow below Microsoft web resource.It requires you to have admin access to windows machine and then start SQL server with -m option and create login in SQL server and grant it admin privileges. http://msdn.microsoft.com/en-us/library/dd207004.aspx Hope this helps


0

No, auditing only reflects the one server that is running the audits. If you want to centralize audit results from multiple servers, you'll need to combine that data yourself. Keep in mind that if someone really wanted to circumvent this, they'd simply add another replica on any server they want, do the querying they need, and then destroy the replica. An ...


1

There's several different questions in here: Q: Why weren't the queries spilling before? They were, but SQL Server Management Studio didn't surface this as a clear error prior to SQL 2012. It's a great example of why when you're doing performance tuning, you have to go deeper than the graphical execution plan. Q: Why do queries spill to disk? Because SQL ...


0

Catalog and index are separate. What do you see in sys.fulltext_indexes? Did you confirm data there after the index was created? You could check for recent Object:Deleted events in the default trace: DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc' FROM ...


3

DB Copy has a number of limitations as describe at: http://msdn.microsoft.com/en-us/library/ms188664.aspx This includes not supporting the copying of System databases, Databases marked for replication, or any database not in a normal status. However, it does support moving logins, jobs, and so forth. The typically best way to copy a database with the ...


2

If you are using Transactional Replication then yes, this configuration is supported. This is covered in Using Multiple Versions of SQL Server in a Replication Topology: For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.) Since ...


1

Rebuild the indexes on the table to a filegroup instead of a partition schema. Do so in the following order: CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on the cluster index CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on all non clustered indexes (one at a time) If you can take some downtime, consider scripting and dropping the non-clustered ...


0

Some of the other operators may make this easier to understand, but perhaps slower to execute: select * from ( select <whatever> from TableA INTERSECT select <whatever> from TableB ) as TableC UNION ALL select <whatever> from TableB where <column> IS NULL


0

Could a recursive CTE satisfy your requirements? It can do the UNION ALL and recursion limiting, too.


0

Not working Statement TableA Inner Join TableB on TableA.ColumnA = TableB.ColumnA OR TableB.ColumnA = NULL Working Statement TableA Inner Join TableB on TableA.ColumnA = TableB.ColumnA OR TableB.ColumnA IS NULL TableB.ColumnA = NULL was not working as expected !!!


1

These are the steps of the SQL Agent Job: detach the database, copy MDF and LDF files to the destination, then attach both source and destination to their respective instances. It is possible that there was something preventing the detach. You cannot detach a database that is replicated/published. A database snapshot exists on the database. It is being ...



Top 50 recent answers are included