SQL 2012 :: Script To Trace FK Between Different DBs?

Apr 22, 2014

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] ....

View 2 Replies


ADVERTISEMENT

SQL 2012 :: Trace File Sizes

Jul 30, 2014

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.

View 7 Replies View Related

SQL 2012 :: Error Log And Trace File

Sep 2, 2014

Where would i find my particular database Error log file, Event log file and Trace file etc?

View 5 Replies View Related

SQL 2012 :: How To Trace For Parallel Queries

Nov 6, 2015

what 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?

View 4 Replies View Related

SQL 2012 :: Turn Off Server Audit Trace

Mar 21, 2014

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 Related

SQL 2012 :: Deadlock Analysis From Trace File

Sep 10, 2014

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 Related

SQL 2012 :: Profiler - Trace Direct Queries Only?

Oct 21, 2015

Is 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.

View 1 Replies View Related

SQL 2012 :: Server Trace With Missing Column Statistics?

Jul 23, 2014

Getting events in the default trace saying missing column statistics on a column...

1.The column is the primary key column ( identity )

View 2 Replies View Related

SQL 2012 :: Server Profiler - Failed To Start A New Trace

Aug 3, 2014

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.

View 3 Replies View Related

SQL Tools :: How To Trace Or Monitor Previous Actions In 2012

Sep 12, 2015

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 Related

SQL Server 2012 :: Query Execution Not Showing Up In Profiler Trace

Aug 22, 2014

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.

View 9 Replies View Related

SQL 2012 :: Trace Flag To Capture Error Messages Like PK Violation

Apr 24, 2015

Is 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..

View 3 Replies View Related

SQL 2012 :: Does Trace Flag 2371 Indirectly Reduces CPU Usage

Oct 13, 2015

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.

View 2 Replies View Related

SQL 2012 :: Impact Of Trace Flag 272 On Server Apart From Disabling Identity Jump

Sep 5, 2013

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.

View 8 Replies View Related

SQL 2012 :: Delete Particular Trace File And At Same Time Keeping Directory Dynamic

Mar 13, 2014

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"

View 4 Replies View Related

SQL 2012 :: Adding T272 Trace Did Not Correct Identity Column 1000 Jump

Jun 15, 2015

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

View 3 Replies View Related

SQL 2012 :: Trace Creation Time Is Different From Time Check Creation

Oct 8, 2014

We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.

for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".

View 0 Replies View Related

How Do I Trace In Sql 7?

Jan 5, 2000

I couldn't find a "sql trace" utility like in sql 6.5... Does anybody know how/if I can do this?

View 2 Replies View Related

SQL Trace

Dec 27, 2003

if i dont use the trace to record all the sql transaction. and someone execute the delete command to delete one of the table (ofcourse the person have high enough permissions to do that). is there anyway i can find out who that user's ID is to run the deleted ?? Thanks:D :p

View 2 Replies View Related

How To Trace SQL

Jun 21, 2004

Hi,

I'm trying to debug a vendor package and would like to turn on JDBC trace (either client or server side). The only information I found is DBCC TRACE which seems not very useful (I even don't know where the trace result located). Any help is appreciated!

Env: Windows Server 2003 & SQL Server 2000 8.00.818.


Jeff Li

View 5 Replies View Related

How To Trace MS-SQL ....

Feb 21, 2007

Guys,


How to trace/find out some one has dropped a database from my QA environment? Unfortunately we havent enabled the trace on this server.
We havent find any useful information from SQL Server logs also.

Can any one reply me how to find the details of who dropped the DB, when? is there any query/SP/command/tools avaialble?

Regards
BSR

With Regards
BSR

View 5 Replies View Related

Trace

May 17, 2007

hi all..
i have 2 tbl..

-- Prepare sample data
DECLARE@tbl1 TABLE (box varchar(10), loc varchar(5) )

INSERT@tbl1
SELECT'P1', 'aa' UNION ALL
SELECT'P1', 'bb' UNION ALL
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'

DECLARE@tbl2 TABLE (box varchar(10), loc varchar(5) )

INSERT@tbl2
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'

--expected result
SELECT 'P1' as box, 'aa' as Location, 'aa' as HeaderLoc UNION ALL
SELECT 'P1' as box, 'bb' as Location, 'aa' as HeaderLoc
how do i trace from @tbl1, that has 1 Box with more than 1 Location?
in this case P1 has 2 distinct location (aa & bb).. and left join in the loc from @tbl2 just to retrieve the loc for that box..

~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View 6 Replies View Related

Trace SQL

Jul 23, 2007

How do you set up the trace in SQL?

thanks


sorry, I am an Oracle guy trying to learn SQL

View 1 Replies View Related

Help With Trace

Jul 20, 2005

Hello:I am working with SQL Server 2000. I have a stored procedure that creates 3temporary tables (#temp1, #temp2, #temp3). When I view the trace of thestored procedure I see entries that have Object Created #temp1, ObjectCreated #temp2, Object Created #temp3. How can I fix the trace so it doesnot report any objects that get created in the tempdb.Any help will be appreciated.Alee

View 1 Replies View Related

ASP.Net Trace File

Jul 2, 2004

When we enable tracing for a web application where exactly the file or content of the file "Trace.axd" saves?

View 4 Replies View Related

Trace Flag Help

Apr 2, 2001

Hi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.

View 1 Replies View Related

Trace Flags

May 1, 2001

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.

View 1 Replies View Related

Table Trace

May 29, 2001

Hi There,
Is there any way to identify how many times a table is accessed by different users in a day from a database.

Thanks,
Madhu.

View 3 Replies View Related

Trace On Deadlocking

Jun 21, 2001

Hi all,

I was wondering how much of an overhead would Running this
trace (1204) have over the system. Will my perfomance
degrade significantly. If yes, by what percentage ???

The trace is 1204 to keep a watch on DeadLocking

I am running this command
C:mssql7innsqlservr -T1204 /dc:mssql7datamaster.mdf

How much performance degrade are we talking about here ?
The Application is Peoplsoft and the db sizes is about 10
Gb !!


Regards

Girish

View 2 Replies View Related

Trace Out The USER

Jun 27, 2000

SUPPOSE A THIRD PERSON WHO (MODIFIES THE STRUCTURE OF A TABLE) OR (DOING SOME MODIFICATIONS ON A CERTAIN COLUMN), HOW THE SQL SERVER DBA WILL FIND WHO HAS DONE THIS. WHETHER THESE MODIFICATIONS WILL BE STORED IN SYSTEMS TABLE?. THIS IS MY QUESTION. CAN ANYBODY GIVE SOLUTION TO THIS PLEASE?. THANKS ---- Srinivasan.

View 2 Replies View Related

Can I Tell What Trace Flags Are On?

May 22, 2000

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

View 1 Replies View Related

Creating A Trace

Mar 1, 2001

Hello,
I have to create a trace to monitor how the stored proc.are performing.
The sp are for update/select/insert.
What all counters should I monitor?
The sp are called in from Java applications thru weblogic server and some of them are taking as long as 30 secs to fetch 80 records!
Any help/thoughts appreciated.

View 1 Replies View Related

Using Trace Flags

Sep 6, 2000

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved