396 reputation
128
bio website stackexchange.com/users/…
location GA, USA
age 43
visits member for 2 years, 8 months
seen Jul 22 at 0:28

Engineering Manager

  • Software engineer
    • C, C++, C#, various BASIC's, webby stuff, some assembly, others
    • Windows, Linux, Android, PIC, Arduino
  • Electrical engineering
    • Motor control, control systems, digital and analog
  • Robotics
    • Battlebots participant
  • MCITP SQL Server DBA
  • Amateur Radio operator (Technician class)

Nov
15
awarded  Yearling
Mar
6
awarded  Fanatic
Jan
13
comment Deadlock error isn't returning the deadlock SQL
From msdn.microsoft.com/en-us/library/ms188396.aspx: Behavior Changes: In SQL Server 2000, a simple DBCC TRACEON (1204) is enough to enable deadlock reporting to the error log. In SQL Server 2008, you must enable the flag globally because the session-level flag is not visible to the deadlock monitor thread.
Jan
13
awarded  Commentator
Jan
13
comment Deadlock error isn't returning the deadlock SQL
Thank you, I stand corrected. I have always reviewed deadlocks manually, and found the output from 1204 satisfactory, so that is what I use. I presumed the Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format... to actually mean that it was XML... silly me.
Jan
13
comment Deadlock error isn't returning the deadlock SQL
1204 is text, 1222 should be XML.
Jan
13
comment Deadlock error isn't returning the deadlock SQL
Trace flag 1222 will enable the XML version to be logged. Which is handy as it isn't transient, it persists in the SQL Server log.
Jan
13
comment Deadlock error isn't returning the deadlock SQL
Use the configuration manager. Under SQL Server Services, right click and open properties. Go to Advanced tab, startup parameters. You will have entries for the location of the master database files and such. Add ;-T1204 for the trace flag to end and restart the service.
Jan
13
comment Deadlock error isn't returning the deadlock SQL
Are you able to restart the service? If you are able to bounce the service, you can add add trace flag 1204 to your startup parameters to log the details of the deadlock to the SQL Server log. > 1204: Returns the resources and types of locks participating in a deadlock and also the current command affected. > >Scope: global only
Jan
5
comment Is there a way to ensure that a SQL Server trigger will be executed?
While I cannot find it documented elsewhere at the moment, the documentation for ROLLBACK TRANSACTION (msdn.microsoft.com/en-us/library/ms181299.aspx) states "@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)". I know I have also seen it elsewhere but I am at a loss as to where and don't have the time to do a more in depth search.
Jan
5
comment Is there a way to ensure that a SQL Server trigger will be executed?
The trigger participates in the ambient transaction, but creates an implicit nested transaction even if no BEGIN TRANSACTION is ever explicitly declared. Check @@TRANCOUNT.
Jan
4
answered Is there a way to ensure that a SQL Server trigger will be executed?
Dec
27
awarded  Enthusiast
Dec
12
awarded  Critic
Dec
9
awarded  Editor
Dec
9
revised Should I lock the table during delete
Corrected typo, to delete records older than 31 days as described. Grammar edit to fulfill the 6 character requirement
Dec
9
suggested suggested edit on Should I lock the table during delete
Dec
5
comment How to programmatically remove a push subscription?
Now that the server is back online, this is where the cleanup comes into play... although, are you saying that the subscriptuion from the original server reappeared on the subscriber? Did you reinitialize the subscription from the publisher when it came back up?
Dec
5
comment How to programmatically remove a push subscription?
Well for one, that proc removes ALL replication info, and is not reversible. So if that database has multiple subscriptions or other publications, they will all be dropped.
Dec
2
answered How Commit works in a transactional push replication with updatable subscribers