Tag Info

Hot answers tagged

8

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember them being effective was in SQL Server 2000, so it is not terribly surprising that you find they are ignored in SQL Server 2012. For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is ...


6

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn. You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably ...


5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


5

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead: CREATE NONCLUSTERED INDEX [IX_NotSynchronized] ON [dbo].[PackageEvents] ([PackageID]) INCLUDE ([EventDate], [EventDescription], [EventID], ...


5

Even if you have plenty of DRAM, tempdb may still be used. This happens in a few situations: Snapshot isolation: Using this feature can create a lot of tempdb activity. Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is ...


4

You could use DATETIMEFROMPARTS (Transact-SQL) select datetimefromparts( datepart(year, getdate()), datepart(month, getdate()), datepart(day, getdate()), datepart(hour, @testTime), datepart(minute, @testTime), ...


4

As Aaron Bertrand mentioned, expressions are very tricky to predict. If you dare go there, you could try to gain some insight using the following snippet: DECLARE @number SQL_VARIANT SELECT @number = 0.15 / 360 SELECT @number SELECT SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType, SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength, ...


4

Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring. I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while ...


4

I think this does what you need. USE 'yourDB' GO SELECT OBJECT_NAME(p.[object_id]) BlockedObject FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id ...


3

I believe you could use database snapshots. Though snapshot can not replace full database backup, since it contains only amended pages from the moment the snapshot was created. Essentially, you restore the production database onto the development server, take a snapshot, perform whatever test work you need to. When you need to return to the original state ...


3

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with ...


3

In my case I was able to narrow down my trouble by working with my sysadmin about how the group was defined. Knowing that the SQL Server was hosted on a machine that had joined another domain and was running with a user account on that other domain, we looked at the Group Scopes in active directory. My user could authenticate properly after the scope was ...


3

Without seeing the actual query and plan, we are shooting in the dark. Depending on how the query is actually written, it might benefit from an index such as: CREATE NONCLUSTERED INDEX [IX_IsSynchronized_PackageID_etc] ON [dbo].[PackageEvents] ( [IsSynchronized] , [PackageID] ) INCLUDE ( [EventDate] , [EventDescription] , [EventID] ...


2

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement. SELECT @NewMetricID, NULL, sm.sortorder, ISNULL(sm.MetricOrder, 1), ISNULL(sm.CategoryOrder, 1), sm.RptCurrentGroup, 'System', ...


2

I'm answering my own question here because we finally figured out the problem. Short Version: We added a third column to the nonclustered index. Deadlocks disappeared. Long Version: First, check out James Rowland-Jones' dynamite blog post about lock hashing collision (My explanation will be nowhere close to the quality of his). From the blog post: ...


2

On server side trace DURATION is the total execution time (in ms) of a query. CPU column will provide you time on CPU (total of one or multiple CPUs). While DURATION - CPU = IO Time So If RPC-Completed time for single procedure call or total time is according to benchmarks then problem could be on network side. Where is actual problem. To get this you must ...


2

Are you familiar with the SELECT .. INTO syntax? It's a useful trick for deconstructing situations like this because it creates a table on the fly with just the right data types for the given SELECT list. You can break up your calculation into its constituent steps, applying SQL Servers' precedence rules as you go, to see how the definition changes. ...


2

Notwithstanding the excellent answers already added to this question, there is an explicitly defined order of precedence for conversion of data types in SQL Server. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the ...


2

I don't have hands-on with this but have observed someone else go through the same thing. To keep the old history you might want to migrate it to a separate table. When the jobs get created, the default retention period is 72 hours. Unfortunately I don't know how to change that because the system procedures also run the job for you, before you have a ...


2

Considering that: REPLACE, PATINDEX, CHARINDEX and even LIKE have not been updated to fully and properly deal with LOB/MAX types; and, you need to repeat this process across hundreds of databases and against multiple instances I would strongly recommend you look at doing this outside of SQL Server, probably in PowerShell or C# (not CLR inside SQL Server, ...


2

Yes, it's possible: select Id, -- Remains as in table cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory from dbo.MyTable; Got myself the hassle of creating similar wrappers recently to stop Entity Framework messing with views' keys...


2

The supported upgrade path does not go directly from 2000 to 2012. You will have to have an intermediate step where you upgrade or restore those DBs to a 2005 or 2008 R2 instance. Once you've done that, you can upgrade to 2012 or take a backup of the DBs in 2005, then restore them on to 2012. We are migrating to 2014 as my company is on 2005 SP3 right ...


2

Seems like an appropriate way to do it. Create a logging table: CREATE TABLE dbo.LogSpace ( dt DATETIME NOT NULL DEFAULT SYSDATETIME(), dbname SYSNAME, log_size_mb DECIMAL(22,7), space_used_percent DECIMAL(8,5), [status] BIT ); Do this before and after your load: INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status]) EXEC ...


2

The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates. To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of ...


2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


2

I can't think of an easy way to do it other than fixing up the strings, eg DECLARE @yourString VARCHAR(MAX) = '%3CRoot%3E%3CTMN%3E2014-10-23+10%3A00%3C%2FTMN%3E%3C%2FRoot%3E' SET @yourString = REPLACE ( @yourString, '%3c', '<' ) -- Replace 'less than' symbol SET @yourString = REPLACE ( @yourString, '%3e', '>' ) -- Replace 'greater than' symbol ...


1

I think you are just missing the @PersonID in the INSERT statement and there is no need to join to PersonLog. In other words, you should do this: INSERT INTO dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId) SELECT Person.PersonId, Person.PersonName, Person.Address, Person.Mobile FROM dbo.Person WHERE dbo.Person.PersonID = @PersionID /* Add this ...


1

Because there are only 8208 pages allocated to MEMORYCLERK_SQLLOGPOOL in the excerpt not sure kb2769594 is the culprit/fix. Also no reference to internal pool or HADR as in the kb2769594 example. https://support.microsoft.com/kb/2769594 So I think culprit/fix may be something else. Surprising that no large pages allocated at all - I'd expect some for ...


1

SQL Server in picture is patched to SP1. Microsoft has released SP2 for SQL Server 2012 and there was fix for such type of error. As per Microsoft Article reason is Microsoft SQL Server 2012 may experience some out-of-memory errors because of an internal memory leak. In this situation, the leaked memory is consumed by the MEMORYCLERK_SQLLOGPOOL memory ...


1

Ok I got the answer. I used the following: EXEC sys.sp_cdc_add_job @job_type = N'cleanup' ,@start_job = 0 ,@retention = 52494800; This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)



Only top voted, non community-wiki answers of a minimum length are eligible