Tag Info

New answers tagged

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


1

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


3

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.


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


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


0

Partitioning is ideal for this sort of thing. For that you would need to define a key, however. If you really just want to slice a big pile of rows into small piles how about something like select top 1000000 <your columns> into dbo.SubTable1 from dbo.ReallyBigTable; delete dbo.ReallyBigTable where ID in (select ID from dbo.SubTable1); Repeat ...


0

It turns out that changes in table structure (schema) is automatically handled, so changes in the primary database should be also reflected in the secondary ones. Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that ...


0

First, do your have columns of data type TEXT or NTEXT in your database? If select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('ntext','text') and t.table_name=c.table_name and t.table_type='BASE TABLE' returns any rows, you probably lost a lot of data during this attack, as the ...


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)


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


1

My first thought was to restore the backup. At 1TB this becomes impractical, however. Log shipping would not allow you to write to the pre-prod instance either. I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in ...


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


0

I did not find the cause for this, but the following while not best solution does enable command. It is my hope it points to the problem and someone can provide a better answer. I use Microsoft SQL Server Management Studio (Administrator) Under localhost Security logins Double click user, select Server ...


1

You can create a simple SSIS package: Here's a high-level how to: Create a OLEDB connection to the database in the connection manager Drag a Data Flow Transformation into the Control Flow and then click on it to get to the data flow. Drag a OLEDB Source from the toolbox into the data flow, and edit it so it connects to your desired table using the ...


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


0

You should enable trace flag 1802 on your database servers. This will allow SQL Server to retain inheritable permissions when you detach a database so you won't experience this issue going forward: http://support2.microsoft.com/kb/922804 DBCC TRACEON(1802, -1) Add it to the startup parameters of the SQL Server service ...



Top 50 recent answers are included