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 RepliesI restarted the sql server after c2 audit was enabled and now i can not start the instance getting this error below. how do i bring the sql server up?
Cannot start C2 audit trace. SQL Server is shutting down. Error = 0x80070003(The system cannot find the path specified.)
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 Replies View RelatedHi,
Could anyone assist me with this issue, nothing has changed on the server except trying to restore a db from another sql server to the one with the error (SQL01 to SQL02)
SQL02 Server does not startup, i get this error:
Cannot start C2 audit trace. SQL Server is shutting down. Error = 0x80070003(The system cannot find the path specified.)
Then I try to apply the -f parameter on service start up, and i get this error:
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
Could anyone please advise me on some solution?
Much appriciated
Gino
Hi,
I've created a few audit trace sessions and each of them is producing files.
The procedure I used is listed below:
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'sp_Turn_Audit_On' AND type = 'P')
DROP PROC sp_Turn_Audit_On
GO
CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
exec @rc = sp_trace_create @TraceID output, 2, N'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info.trc', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
begin
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 13, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 16, @on
exec sp_trace_setevent @TraceID, 20, 17, @on
exec sp_trace_setevent @TraceID, 20, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 46, 1, @on
exec sp_trace_setevent @TraceID, 46, 6, @on
exec sp_trace_setevent @TraceID, 46, 9, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 13, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 16, @on
exec sp_trace_setevent @TraceID, 46, 17, @on
exec sp_trace_setevent @TraceID, 46, 18, @on
exec sp_trace_setevent @TraceID, 47, 1, @on
exec sp_trace_setevent @TraceID, 47, 6, @on
exec sp_trace_setevent @TraceID, 47, 9, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 13, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 16, @on
exec sp_trace_setevent @TraceID, 47, 17, @on
exec sp_trace_setevent @TraceID, 47, 18, @on
exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 13, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 16, @on
exec sp_trace_setevent @TraceID, 104, 17, @on
exec sp_trace_setevent @TraceID, 104, 18, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 13, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 107, 16, @on
exec sp_trace_setevent @TraceID, 107, 17, @on
exec sp_trace_setevent @TraceID, 107, 18, @on
exec sp_trace_setevent @TraceID, 109, 1, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 13, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 16, @on
exec sp_trace_setevent @TraceID, 109, 17, @on
exec sp_trace_setevent @TraceID, 109, 18, @on
exec sp_trace_setevent @TraceID, 110, 1, @on
exec sp_trace_setevent @TraceID, 110, 6, @on
exec sp_trace_setevent @TraceID, 110, 9, @on
exec sp_trace_setevent @TraceID, 110, 10, @on
exec sp_trace_setevent @TraceID, 110, 11, @on
exec sp_trace_setevent @TraceID, 110, 12, @on
exec sp_trace_setevent @TraceID, 110, 13, @on
exec sp_trace_setevent @TraceID, 110, 14, @on
exec sp_trace_setevent @TraceID, 110, 16, @on
exec sp_trace_setevent @TraceID, 110, 17, @on
exec sp_trace_setevent @TraceID, 110, 18, @on
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 9, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 115, 13, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 16, @on
exec sp_trace_setevent @TraceID, 115, 17, @on
exec sp_trace_setevent @TraceID, 115, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto noCursor
error:
select ErrorCode=@rc
noCursor:
return
end
Unfortunately, I didn't mention the @stoptime for each of the trace sessions and now I'm unable to delete the trace files. Is there any select statement that will be useful in finding the @traceid for these auditting sessions? Also, how am I able to stop the sessions without stopping the related services?
Thank you in advance.
Best regards,
Alla
Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.
View 2 Replies View RelatedIs there anyway I can turn off delete in SQL server? I want to prevent anyone inadvertently deleting rows in tables. I thought worse case I could have triggers on tables to perform roll back.
View 3 Replies View RelatedHow can i Trace Database Audit specification Enable and Disable. i want to maintain log for enable or disable database audit specification.
View 4 Replies View RelatedGetting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
I am attempting to create a new trace but I get the following error message: "failed to start a new trace".
I have been doing some digging and as I understand it, I had to find the directory Profiler uses for temporary files. So, I typed the following in the command window "SET TMP" and I received the following reply:
C:UsersRossAppDataLocalTemp
Now, according to the forum: [URL] ...
I am supposed to check that the system folder pointed to by the TMP environment variable exists and is not crammed with files.
Well, when I went to the directory C:UsersRossAppDataLocalTemp, it is indeed full of both files and directories. The size is 16.3 MB and has 133 files and 63 folders.
When I had a look at the Environment Variables window and chose TMP the value is "%USERPROFILE%AppDataLocalTemp" which according to my limited understanding is the equivalent to C:UsersRossAppDataLocalTemp.
So, what I am wondering is am I supposed to totally clear out this directory? I am not too keen on doing this because I don't want to stuff my PC up.
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
I am upgrading my application's SQL Server from 2008 R2 to 2012.
As discussed in the below URL I am able to see the Identity jump after I upgrade and the server is restarted.
Now since I cannot afford this and at this moment I do not have the time to create a sequence with NOCACHE and test it again I have to go ahead and add trace flag 272 in the start up parameter as this is the only solution which I can implement and even rollback without much hassles.
[URL] ....
What I got to know, this flag will disable the new feature of IDENTITY property that has been implemented as part of SQL Server 2012 and will make it work like it was doing in SQL Server 2008 R2.
But I want to know implementing this flag would impact any other feature or performance (except the performance of IDENTITY) of SQL Server.
ItemID ItemName Price
1 Test 1 100
1 Test 1 200
2 Test 2 130
2 Test 2 150
3 Test 3 300
3 Test 3 400
...
How to write a query to bring price data to column so the Item is not duplicate any more.
The result should be:
Item ID ITemName PRice1 PRice2
1 Test1 100 200
2 Test2 130 150
3 Test3 300 400
I have scoured the Microsoft forums and the internet to find out how I can generate the output of a CSV report that has double quotes around each value and is comma separated as follows:
"Abcd","123456","Efghi","789012","JKLMN"
If I try to concatenate double quotes around the values in the stored procedure or in the RDL, two double quotes appear around each value as follows.
""Abcd"",""123456"",""Efghi"",""789012"",""JKLMN""
I understand that this is because the default qualifier is double quote. What I see is that every time a double quote appears in a value (along with commas and line breaks), the qualifier will activate. Is there any way to turn this off for double quotes?
If I try to enter: <Qualifier>false</Qualifier>, the word "false" appears as the qualifier instead.
The only way I have found that produces a result similar to what I need ("Abcd","123456","Efghi","789012","JKLMN") is if I add a line break - chr(10) in the RDL in each field. However, this won't work for me because I can't have line breaks in each field in the output.
Note that in SSRS 2005, I was able to produce the report output as I state above by setting the field delimiter and qualifier as follows:
<FieldDelimiter>@?!?@</FieldDelimiter>
<Qualifier>?#^?</Qualifier>
This essentially turned the field delimiter and qualifier off, as the values entered would never appear in the data. I then could add double quotes and commas in the RDL. This used to work in the old version but does not anymore.
I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,
I am having 3 tables, table1 table2 table3
User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).
I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,
TableA
CountryId CountryCode CountryName
1 IND INDIA
Update TableA
Set CountryCode = 'INDI', CountryName = 'INDIA'
Where CountryId = 1
On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.
CREATE TABLE [dbo].tCountry(
CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,
CountryName [varchar](50) NULL,
CountryCode [varchar](50) NULL,
CreateBy bigint
)
EXEC sys.sp_cdc_enable_db
[code]....
--See here, i want to display CountryName as NULL, because i didnt do any changes in this column
--Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.
Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)
I have this script running to list all FK relationship but it's only for single db,tried to make it work for inter DBs
DECLARE @Tname varchar(40) = 'CustAccount' -- <@>< Table Name to trace
-------------------------------------------------
SELECT DISTINCT No ,
ist1.table_catalog AS [Parent DB],
ist1.table_schema AS ParentSchema,
ParentTable ,
ReferencedColumnName ,
ConstraintName ,
link,
ForeignTable,
ForeignKeyColumn ,
[Code] ....
I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.
What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.
Where would i find my particular database Error log file, Event log file and Trace file etc?
View 5 Replies View Relatedwhat the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.
MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.
I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :
0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?
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 RelatedIs there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.
Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.
how to trace or monitor previous actions in sql server 2012. Like previous using delete command . I want to know who is used that command in server.
View 2 Replies View RelatedIs there a dbcc flag that will capture all error messages in the log
e.g. when inserting data into a table, a PK violation occurs throwing, Msg 2627
..similar to trace flag 1222 to capture deadlock info..
After carefully analyzing the situation for almost a month, I pulled the trigger and made an exception at work; we did enable trace flag 2371. We have some tables with billion of rows and outdated statistics causing horrible plans. Tried several methods to update those, but did not solve the problem or was too CPU intensive, causing other issues.
Anyway, one of the side effects I am seeing so far is average vCPU went down by almost 40%. Nothing out of usual (besides the flag) has been enabled or was executed. So my assumption is, CPU hungry plans are now gone or reduced.
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
del $(ESCAPE_SQUOTE(SQLDIR)) + "LogTestTrace.trc"
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
After SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?
Below the log showing the flag being added to the error log:
LogDateProcessorInfoErrorMSG
2015-06-15 22:29:53.850ServerRegistry startup parameters:
-d E:DATAmaster.mdf
-e E:logERRORLOG
-l E:DATAmastlog.ldf
-T 272
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies View RelatedHi everyone.
I've just installed sql server 2005 (evaluation) and i need to turn the option of using sp_send_dbmail on. I tried books on line but didnt manage to understand how it is done.
Thanks
Some how SQL Server 2005 database mirroring was partialy set up on one of my servers. I know this because there is traffic from the server to port 5022 for IP addresses 1.1.1.[234] (which means the last octect is either 2, 3, or 4).
I'm getting complaints from the network manager about the traffic and I want to turn this off, but I can't find any documentation on this topic. I've checked the database configuration and I can't find any configuration for mirroring, just the traffic on the TCP port.
Does this mean a background processes (service) is running that needs to be killed or is it more complex than that...
I made B server which get logs from primary server A as a secondary server in the log shipping solution.
it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
The database is in high-safety mode.If off Mirror and Witness both the Principal goes into Disconnected / In Recovery. How turn online this database? Similarly, if off and Principal Witness both the Mirror goes into Disconnected / In Recovery. Is it possible to turn online database?
View 7 Replies View RelatedI have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g