To get deadlock victim alert do we need to turn on deadlock trace flags or if I create an alert and if there's any deadlock incident happen, it will throw alert (if no deadlock flag is set)?
View 4 RepliesHi ,
I want to trace the deadlock information. I am enabling trace flags 1204 ,1205. Is there any difference in setting these trace flags by using DBCC
TRACEON and by setting them at command prompt by statrting the SQL server with SQLSERVR command. Actually i don't want to bring the server down. I want the information to be logged to Error log. Any help is greatly appreciated.
If I issue a DBCC TRACEON (1205) from a query window from a client machine.. Does it continue to run when I close that connection (spid).
Is there a way for me to tell what traceflags are currently on?
Thanks,
Dano
To solve a problem I encountered with Restoring from Backups in 6.5, I had to install a hotfix and thereafter do the load using Trace Flag 3282.I need help on using the trace flag (syntax etc.) Also there is no mention in books online of this particular Trace Flag.
Please help.
I see only a few trace flags and their description in BOL but see a lot of references to various flags like: 1211 and so on... Where can I find all the flags and their descriptive actions?
Thanks, Vinnie
Have Trace Flags changed in SQL Server 7?
I need to use T204 because of getting error:
'ORDER BY items must appear in the select list if SELECT DISTINCT IS USED'
when I try to compile stor proc.
I entered T204 as startup parameter and restarted server
but doesn't seem to work.
Any experience with that?
Thanks
What are the DB mirroring trace flags and how to use ?
View 4 Replies View RelatedWe have the following trace flags present in startup in SQL server 2000:
809
1204
3605
3913
Need to understand if these should be required in SQL Server 2005 + SP2 version. I have run the upgrade advisor tool which indcates that the behaviour of some flags has changed and some other TFs are no longer applicable. Hence, I want to know about the above mentioned TFs.
Regards,
Chetan
I need to enable trace flag
OPTION(QUERYTRACEON 9481)
In one of my views I am having trouble finding where to put it in my existing statement:
USE [pec_prod]
GO
/****** Object: View [dbo].[PEC_Claim_Export_All] Script Date: 8/10/2015 9:18:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[PEC_Claim_Export_All]
[Code] ....
Msg 156, Level 15, State 1, Procedure PEC_Claim_Export_All, Line 56
Incorrect syntax near the keyword 'OPTION'.
During a newly set up on one of our SQL server 2012:
We had enable the trace flags 1117 and 1118 as a good practice using DBCC TRACEON(1117,-1) and similar for 1118.
We have been base lining the server and it came to notice that trace flags are no more enabled.
Property               Value                              CaptureDate
DBCC_TRACESTATUSÂ TF 1117: Status = 1, Global = 1, Session = 0 Â Â Â 2015-10-20 00:00:00
DBCC_TRACESTATUS Â Â Â Â TF 1118: Status = 1, Global = 1, Session = 0 Â Â Â Â Â 2015-10-20 00:00:00
After reboot:
Property                 Value                                  CaptureDate
DBCC_TRACESTATUS      No trace flags enabled             2015-10-21 00:00:02.340
What can be the reason? What can be done to turn them on permanently, if its actually a good bet in enabling so.
Somehow someone turned on a audit on the sql server and it is filling up our hard drive and shutting down sql server eventually. Been trying to google how to shut this audit off but coming up with no via soolution yet. how can I turn this trace off. Each fiel says AuditTrace and date and they happen every other minute. I went into the sql profiler and can pull up the files but how to shut the trace off, it does not say.
View 9 Replies View RelatedWe set our Deadlock Trace on with the following command:
DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO
In 2000 this worked fine but in 2005 we get the following error:
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?
SPID40 ECID : 0 STAEMENT TYPE -> GRANT LINE # 1
INPUT BUFFER :-> SP_EXECUTE, 8
SPID 41 ECID: 0 STATEMENT TYPE : UNKNOW TOKEN # :1
INPUT BUFFER :-> SP_CURSORFETCH 8A G 8 8 8
SPID 40 ECID: 0 STATEMENT TYPE : UNKNOW TOKEN # : - 1
INPUT BUFFER :-> SP_EXECUTE, 8
== LOCK KEY 10:973298577:1 (7A0185EF6B8E)
DATABASE : SASETNWT
TABLE: PSLOCK
INDEX : PS_PSLOCK
HELD BY SPID 40 ECID 0 MODE ‘X’
REQUESTED BY :SPID 41 ECID 0 MODE ‘S’
== LOCK KEY 10:973298577[ ]
DATABASE : SASETNWT
TABLE: PSLOCK
HELD BY SPID 40 ECID 0 MODE ‘IS’
REQUESTED BY :SPID 41 ECID 0 MODE ‘SCHEMA-MOD’
HERE IS WHAT I THINK WHATS GOING ON
First SPID 40 requests a Exclusive lock on the row(key) of table PSlock which is granted by the dB where he is running ‘ Grant sp_execute statement’ !!!
Then SPID 41 request a Shared lock on the table Pslock which is granted by the db where spid 40 is running “ Sp_cursorfetch statement”
Now we have two lock on Pslock table , one by spid 40 with an exculsive lock and other by spid 41 with shared lock.
then spid 40 requests a ‘Schema- mod’ lock on Pslock table which I guess is a table lock
and we have have a dead lock situation here
is the above explanation right ?????
regards
Girish
I am getting deadlock in my production, i was taken deadlock information from trace file , i found deadlock graph but i am unable to find exact scenario . I am attaching deadlock trace file.
View 5 Replies View RelatedHi, I have no idea what i'm doing wrong: Tried to gather more detail information about Deadlock (error # 1205) set the trace flag 1204 - ON (tried 1205 - ON as well) nothing happend, still the same message outgoing "Your transaction (process ID #13) was deadlocked with...." with no any detail.
If anybody met the same problem before, HELP please.
Maybe the information goes to some place other than Error Log file?
Thanks,
Dima
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies View RelatedIs the process of using integer data types to represent multiple values via the use of bit flags bad practice? It seems to go against the rules of normalization in a single field can represent multiple values. On the other hand that since these values can be tested for via bitwise operations, that it's not entirely bad.
Any insight would be appreciated.
Thanks
Can someone explain me how does work that flags?
# “Auto Close Flag�
# “Auto Create Statistics Flag�
# “Auto Shrink Flag�
# “Auto Update Flag�
ok ok, I suppose that they shrink, update statistics and stuff automatically, but... when? every second? is it ok to leave all them as true?
thanks
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure
View 4 Replies View RelatedI have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.
This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?
Its transact SQL and the formulas I use in excel are:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2>=C2-1/24)*($C$2:$C2< C2+1/24))> 1,0,1)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
How I can do this in sql??
The columns above do not relate to the actual columns I use, just an example.
What happens when you add the Ignore Case flag into the mix?
I'm having a hell of a time - I'm dealing with an SCD situation using TableDifference component and I have both existing dimensions and new data coming in, each go through identical Case-Insensitive/Sort with remove duplicates, but I'm getting identical new and deleted records detected - I think because of ordering issues. I'm still trying to whittle the test case down, but I think data from all around the records I'm investigating seems to get sorted in between them, so I'm having trouble getting a small test case built.
I think the mixed case data is the root of the problem, and I think the design is bad, but before I go back to the technical lead, I need to understand enough to show that you cannot take two pipelines sorted and de-duped case-insensitively and then do a case-sensitive table difference operation.
This may sound silly, but I'm calculating how much someone owes over time verses how much they have paid for that period. So if they owe more than they have paid the result is an under payment amount like -$100. Then I need to add the -$100 to the new payment due, let's say $100. So the total Due is $200. But if I add -100 to 100 that equals 0.
Any suggestions?
How do you turn LOGGING OFF for a DTS package????
DB2 is a simple "LOAD DATA REPLACE NO LOG"... (can SQL Server make it any more difficult to find this option to turn on/off??!!!)
frustrated!!!!!!!!!!!!!!!!
thanks-
Hello,In SQL Server 2005 Enterprise, I can change the CPU affinity viaManagement Studio. Is there a way that I can change these settings viat-sql?I wish to use all my cpu's at night when my data warehouse builds andthen durning the day reduce the number of cpus for SQL so that theapplication can get more time.TIARob
View 2 Replies View RelatedEver since installing SQL Server 2005 Express Edition the XP Task Manager shows €œsqlservr.exe€? running normally, as a €œNETWORK SERVICE€? consuming 26MB of memory. This is true even after a cold reboot. I€™d like to turn SQL off since I am not currently planning on using it. I tried detaching a couple of projects I had started, as part of tutorials, but that did not make a difference.
So how do I turn SQL Server 2005 Express Edition off without removing it from the system, which I do not want to do?
Thanks.
Thomas
View 8 Replies View Related
Can anyone help me with this problem. I have old data that I need to insert into a new table. I need to keep the old identity values and then turn them on again for the new data.
I'd be very grateful for any help.
Thanks
A.
I am running sqlserver 2000 Enterprise edition on Windows NT 4.0 service pak 6.
When ever I run a long query, the transaction log seems to fill up. How do you turn this transaction log off? Please let me know ASAP.
Thanks,
Ranjan
I have this table:
ProductionID ResourceID Time
------------ ---------- -----
WO001 200012 5
WO001 200011 1
WO002 200013 2
WO003 200012 3
WO003 200032 4
and I want it to look like
ProductionID 200011 200012 200013 200032
------------ ------ ------ ------ ------
WO001 1 5 NULL NULL
WO002 NULL NULL 2 NULL
WO003 NULL 3 NULL 4
I am looking for a smart solution without hardcoding the column names (e.g. CASE WHEN ResourceID = '200012' THEN Time END as ['200012'], etc...), as there can be up to 15 different resources involved; usually they aren't but I want to keep the result as slim as possible without tons of columns containing nulls.
I have a nvarchar(256) column and when I am using C# code to add data to it. When I enter data over 256 characters long, it automatically truncates. When I try to do this is the Query Editor, it will not allow me to and terminates with the error "Binary data or string would be truncated"
Is there a way to turn this automatic truncating off?
I have one data flow task which creating a database table (tempTable) and other task will migrate data from other database into this table. Now I add third task which will read the data from this tempTable. If tempTable is not in the database, then validation will failed. Is there a way to turn off validation at runtime.
In my case, my package running every night. I would like to delete this tempTable at end of package run. Now I have to pre create this temp table, and run package and leave the temptable around for the next run to pass validation problem.
Thanks!
Jun Fan
How do I return a list of sold CD's, showing the last once (least sold) first? Below sql, ordered by DESC, returns a CD-list from 1, 2, 3 ... 49, 50, 51. (but I would like the turn it like this)
Least sold cd
51 Martin
50 Elvis
49 Krall
SELECT top 10 SUM(Cd.Price) AS Sales, Artist.Name
FROM Cd INNER JOIN
purchase ON Cd.Cd_id = purchase.Cd_id INNER JOIN
Cd_artist ON Cd.Cd_id = Cd_artist.Cd_id INNER JOIN
Artist ON Cd_artist.Artist_id = Artist.Artist_id
GROUP BY Artist.Name
ORDER BY SUM(Cd.price) DESC(Due to "=RowNumber(Nothing)" numbers are return instead of salesfigures.)