Hot answers tagged

10

Having an open transaction by itself will have almost no consequence. A simple BEGIN TRANSACTION -- wait for a while, doing nothing -- wait a bit longer COMMIT will, at worst, hold a few bytes of status values. No big deal. Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be ...


4

You are performing an index rebuild on Standard edition. As documented here you would need Enterprise or Developer edition to see parallelism for this.


4

Incomplete transaction may hold large number of locks and cause blocking When a transaction is not completed either because a query times out or because the batch is cancelled in the middle of a transaction without issuing a COMMIT or ROLLBACK statement to complete the transaction, the transaction is left open and all the locks acquired during ...


3

Your largest consequence will be blocking of the objects used in the transaction. Especially if you assume your users are inserting data, then that long running transaction could include SELECT statements on commonly used tables. Your users' update statements may not be able to get the necessary lock required to complete their updates or inserts. A ...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


3

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production. Setting that aside: There are a number of tools out there that claim to compare schema, and ...


2

You can get name of well-known sid https://support.microsoft.com/en-us/kb/243330 with powershell script $objSID = New-Object System.Security.Principal.SecurityIdentifier ("S-1-5-18") $objUser = $objSID.Translate( [System.Security.Principal.NTAccount]) $objUser.Value Is there any problems with giving MS Sql Server sysadmin role to 'NT AUTHORITY\SYSTEM' ...


2

Installing service packs usually goes like this: detailed step by step guide It shouldn't affect databases, but you may want to test installing SP3 on the test server first. It will help you get an idea how long it takes (management will want to know that beforehand). It will also give you a chance to find out if any of your applications (or anything else) ...


2

This query will give you a list of your table names and the field name into a temp table. IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData GO CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max)); DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME'; INSERT INTO #BaseData (...


2

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ? IMHO, NO - since you are on sql server 2012. Why ? SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is ...


1

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


1

This will return the name of the view(s) and the definition (the CREATE statement) for all views that your table is in. SELECT name, definition FROM sys.objects o JOIN sys.sql_modules m on m.object_id = o.object_id WHERE definition LIKE '%<Your table name>%'


1

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command. These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll ...


1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled back. The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will ...



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