DB Engine :: Trace Flags 1117 And 1118 No More Enabled After Server Got Rebooted
Dec 1, 2015
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.
We 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.
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'.
Hi , 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.
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?
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)?
I have a windows 2000 machine with SQl 2000 installed. I am experiencing some unusal behaviour on this server. BTW I am using terminal services to access the server. The problem is all of a sudden I just get disconnected from the terminal service and the server gets rebooted itself. It's happening while I am trying to open a table, running a query, opening a dts package etc.Is anyone experiencing similar problem or have any idea of what is causing this problem. Any help will be appriciated.
If we want to change something about a custom server side trace in SQL 2012, do we wipe the current trace with sp_trace_setstatus <trace_id>, 2 and then re-create it with the new definition? It looks like there's no way to modify it in place.
I am trying to change the default trace value from 2 to 6, to incorporate rollover and server shutdown. Â I am using this code to create a new trace (with id: 2) with the value of 6 and use that to overwrite the default trace (with id: 1):
  DECLARE @new_trace_id INT;   EXECUTE master.dbo.sp_trace_create    @someinteger = @new_trace_id OUTPUT,    @someinteger = 6,    @someinteger = N'C: raceTestTrace';
Then I disable the default trace. Â I am then using this code to overwrite the default trace (with id: 1) and replace with new value default trace (with id: 2):
    -- get trace status     SELECT * FROM ::fn_trace_getinfo(NULL)     -- stop trace     EXEC sp_trace_setstatus @traceid = 1           , @status = 0      go     -- delete trace     EXEC sp_trace_setstatus @traceid = 1           , @status = 2
Then I enable the default trace. Â It works perfectly (default trace with id 1 is showing value 6) until I restart. Â Upon restart no default traces are enabled, once I run the script to enable the default trace then the values for the default trace (with id: 1) are back, and my value for rollover/server restart is back to 2.
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
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 ?
I 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:
we have error 1117 - Extent chain for object %ID is not correctly linked
Fortunately this is a temporary table, so we can drop it - or so we thought. The table will not drop!"!!
HELP How do I drop a table that does not 'want to be dropped'!!! There are no dependencies and I MUST have this temporary table available for the system to use.
I get the following message when I run a SQL database maintenace routine: I got the following error message when I ran a SQL maintenance routine:
Table Corrupt: Extent is linked in more than one chain. Check the following allocation page and extent: alloc pg#=17408 extent#=17472 status=2 ** Execution Time: 0 hrs, 0 mins, 2 secs **
MS said to run DBCC CHECKDB and DBCC NEWALLOC. CheckDB runs fine with no errors, however, NewAlloc gives me a similar message.
I deleted all data in the corrupt table and tried to drop it but still got a message about a linking problem.
Does anyone know how I can delete this table or a simle fix?
Hello All, I am getting an error 1117 in my server error log during a nightly load into our database. When I get this, I run a dbcc checalloc, and then a checktable and that appears to correct the problem in the short term. The error occurs randomly. I am also getting an error 2541 on about 3 tables. But, SQL Server won`t let me drop and recreate them. My question is...What can I do to correct this problem. Any help would be greatly appreciated.
I have this error isolated in my production environment running SAP/ SQL Server. The options for restoring from database backup out of question. I tried delete records and dropping the table from SQL Entreprise Manager. when I do DBCC updateusage, it always encounters this isolated table and stops the DBCC checks. The update statistics and table consistency DBCC checks are running ok.
Table Corrupt: object id does not match between extent in allocation page and Sysindexes; check the following extent: al loc pg#=4187136 extent#=4187136 object id on extent=12 (object name = sysdepends) object id in Sysindexes=772353966 (ob ject name = SAPWLSFIDX_OLD) DBCC execution completed. If DBCC printed error messages, see your System Administrator.
My company uses some integration software to synchronize tables in our OpenVMS server to our MS SQL 2005 server so statistics and tasks can be performed there instead potentially impacting our main data processing operations.
The problem I'm running into is that timestamps in the tables are stored as date_shipment (datetime) and time_shipment (integer) instead of as a combined datetime_shipment (datetime). Date_shipment contains the date of the transaction with 00:00:00.000 for the time part, while time_shipment has the time of the event stored as an integer, so 8:00 am -> 800, 4:50 pm -> 1650, 8:00 pm -> 2000, etc.
Unfortunately, I'm stuck with what the integration software gives me, so I need to find some way of turning the time integer column into a real time that I can then combine with the date.
Does anyone know of a way to do this? I haven't had any luck while experimenting with different T-SQL yet.
In SQL Server 7.0 I right click on the database, select Tasks, Select backup, then indicate backup of database (complete) to tape. Overwrite (I put tape into NT Server), OK, then it says backup in progress and then I get the message shown in the subject...
Any advice please? I have backed up NT files on this tape before and have tried a second tape...
Is 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.
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?
I am unable to access the default port 1433 on my SQL 2003 server. There is no firewall. I run telnet <ip> <port> and get "Connection failed" which explains my inability to connect to the server for weeks. I have being unable to connect to the server after I uninstalled sql 2000 and reinstalled it on my 2003 server. Some of the things I have checked is Network Configuration in EM. Named Pipes and TCP/IP is installed with port set to 1433. HKLMSoftwareMicrosoftMSSQLServerClientSuperSocketNetLibTcpDefaultPort port is set to 1433. Is there anything I need to configure on the server side to have the default sql server port enabled.
Hello, I have to find out whether my servers have hyperthreading enabled or not???? I am running Windows server 2003 Standard edition on many of my machines. I have to configure the SQL Server, server configuration values according to the Hyperthreading. I know about the CPUcount.exe utility but is there anything else apart from it??????
I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup? Where are the BrokerID's Stored?
