Tag Info

New answers tagged

1

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

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 ...


0

I did not find any source that _110 collations exist. Only _80, _90 and _100 families exist (see e.g. http://msdn.microsoft.com/en-us/library/ms143726.aspx).


0

kill all the connections to the database (sa) , if you don't have the permission , connect to your DB admin and he will do it .(importent to remember to do the kill statment from "master" database). execute this query: USE master go ALTER DATABASE [Your_database_In_Single_user_mode_state] SET MULTI_USER GO it should solve it . ItzikPaz


1

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'. However as @JeroenB said you may have better luck creating a trace or extended events session. You can get the above information and more that way. Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead. You could ...


0

I was having "timeout" issue when trying to connect to SQL Server 2000 using Management Studio 2012. Finally the solution for me was to install Microsoft SQL Server 2000 Service Pack 4 - http://www.microsoft.com/en-ca/download/details.aspx?id=18290 I hope that this solution can be useful for someone having this problem.


0

Here is the MSDN Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns ...


0

This error was resolved by adding a second IP to the listener. The IP address for both subnets cannot be an IP address already in use, i.e. the IP address of one of the nodes. A DNS entry is also not needed since the Listener creates the DNS entry as part of the process.


0

Yes, I figured out that the select statement was returning multiple records (Cartesian product). I was so focused on the output that I didn't pay enough attention to what could be returned with the select part of the insert. I needed another key field in the select part of the insert.


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 ...


1

If you expand the SQL Server Agent in SSMS and double click the Job Activity Monitor you should see what you need.


1

Tested on SQL 2005 only: declare @testTime DateTime set @testTime = '2013-04-23 13:55:06' select cast(substring(cast(getdate()as binary(8)),1,4) + cast(@testTime as binary(4)) as datetime) Why, how? -- These two have the same last 8 hexadecimal digits, -- and vary one digit before that -- This shows that the date and the time ...


0

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


1

Installing Windows Server from scratch would remove any issues from the failed install and ensure that there are no problems going forward when patching or changing the SQL Server instance. If this is going to be a production server removing any chance for downtime in the future should be the biggest concern.


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, ...


0

Unfortunately, there are no settings in SQL Profiler that will display the values the way you want them displayed with your insert .. values statement or any other similar statements. But, by using SP:StmtCompleted and SQL:StmtCompleted you should be able to see when the values are being assigned to the parameters. So you'll have an entry something like ...


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 ...


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: ...


1

Create a new table Table_A_Archive with exactly the same schema as Table_A_Changes. Partition both these tables by, say, one-month date ranges. As data becomes stale in Table_A_Changes swap that partition out and swap it into Table_A_Archive. Your data retention policy can be efficiently enforced this way, too. A view which unions the two tables can make ...


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 ...


0

The actual problem is that the SYSTEM or other container group had not had the NTFS permissions set for the SQL db folder on the SQL data drive, in my case, 'E:\SQLDATA` The root E:\ drive was not accessible, so a specific NTFS permission had to be created for the SQLDATA.


1

There was excellent tool Adept SQL Diff, lightning fast, but it's not supported anymore and does not work on Windows 2008 onwards. So Redgate is the choice. Maybe it's a good time to consider Continuous Integration (CI) and Agile methodology. You will need SSDT (SQL Server Data Tool) and a build software -Team Foundation Server, Bamboo, Teamcity etc. We use ...


1

For just schema changes, it seems quite wasteful to disrupt production and make them wait while you back up the whole database (even if only one table has changed?), restore and test changes in a different environment, then back up and restore again. Get a tool to do real-time schema compare/deploy to keep your two environments in sync without all that extra ...


1

You will need a tool to create scripts that synchronize certain tables in your production database with the tables you have changed in your test environment. Visual Studio 2013 in pro, premium and ultimate editions have a schema and data compare feature. I personally use Red Gate SQL compare.


0

I am not sure whether this could actually be answer but I cannot post this as comment so putting this If you see your application code its like @P1 nvarchar(4000) ,@P2 nvarchar(4000), @P3 datetime2, @P4 nvarchar(4000), @P5 nvarchar(4000), @P6 decimal(38,1), @P7 int, @P8 int) INSERT INTO OBC.MBL_CPU_POS_MSR_ATB ( CRT_S, CRT_PGM_C, CRT_UID, LST_UPD_S, ...


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 ...


4

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


1

Transaction replication would be the solution I would choose for this. More info about transaction replication can be found here: http://msdn.microsoft.com/en-us/library/ms151176.aspx To only choose the columns needed, you need to use a column filter: http://msdn.microsoft.com/en-us/library/ms151775.aspx AlwaysOn availability groups would not work ...


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 ...


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 ...


0

Ownership chaining from procedure does not work with DDL statements and TRUNCATE statement. You can use execute with impersonation (execute as) in your case. The impersonation example for creating a stored procedure is - Create procedure <your procedure> with execute as owner as create type ... Alternately, you can sign your procedure with ...


1

From taking a quick look, it's quite possible that you're actually looking at indexes on different databases. Alternatively, you could have a partitioned table as the docs state that sys.dm_db_partition_stats: Returns page and row-count information for every partition in the current database. Take it back to basics, don't use DB_ID() and actually ...


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 ...


1

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is no real need for the APPLY, eg ;WITH TableA AS ( SELECT 101 as A_ID ), TableB AS (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL SELECT 2 , 101 , 'YYY' ...


1

Looking at a post from the MSDN team, I've come up with a way to reliably get the physical core count from a machine, and use that to determine a good MAXDOP setting. By "good", I mean conservative. That is, my requirement is to use a maximum of 75% of the cores in a NUMA node, or an overall maximum of 8 cores. PowerShell is used to determine the ...


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 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 ...


1

There are a number of them, a lot of articles on the topic if you have google at it. Some of them: Default Schema for Windows Groups User Defined Server Roles Enhancments to Auditing User Contained Databases, authentication without logins. TDE - Transparent Data Encryption Hashing Functions - improved.


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...


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], ...


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] ...


0

If you don't want cdc clean up to run, do not run the system stored procedure to enable it. Then the sql agent job won't get created. exec sys.sp_cdc_add_job 'capture' GO FYI - I have observed disabling CDC clean up long term may cause latency issues within your database. (I would assume you might have that issue too)


1

This is a becoming a big requirement in todays environments and I don't see any easy solution other than using a 3rd party tool. I'm no expert but I was looking at Delphix site the other day and it sounds very interesting. http://www.delphix.com/products/how-virtual-data-machines-work/ It solves the problem with the use of virtualisation and versioning of ...


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 ...


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 ...


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 ...


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 ...



Top 50 recent answers are included