Which Configuration File Can Disable Traces
Nov 29, 2011
When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.
The archive log looks like : SQL Trace ID 2 was started by login "sa"
Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.
I only know how delete a trace, knowing id.
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2
Which configuration file I can disable all SQL Server traces. If I run select * from sys.traces, I just get the default trace:
id = 1
status = 1
path= D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog_32736.trc
max_size = 20
stop_time = NULL
max_files = 5
is_rowset = 0
is_rollover = 1
is_shutdown = 0
[code]....
View 4 Replies
ADVERTISEMENT
Feb 25, 2014
Is it a best practice to disable "Allow files on this drive to have contents indexed" on NTFS drives used by SQL for its data, log, tempdb, etc?
In what I've read it seems to be a best practice for Filestream objects and Flash storage drives. We don't currently use Filestream objects or have Flash drives.
Are there any benefits or drawbacks to disabling this feature on an NTFS drive connected to SAN LUNs under mount points?
View 2 Replies
View Related
May 14, 2007
Hi,
I have a report in which some links navigate to other reports.
If i export the report in Excel format. The Excel file also contain hyperlink what i have created in report. If i click the link in Excel file, it is directing to reportviewer to open that link.
But i need to export this report in Excel format with hyperlink disabled.
I will appreciate if anyone give a solution .
Thank you,
View 10 Replies
View Related
May 13, 2008
I have a report in which some links navigate to other reports.
If export the report in Excel format. The Excel file also contain hyperlink what have created in report. If i click the link in Excel file, it is directing to report viewer to open that link.
But need to export this report in Excel format with hyperlink disabled
( I used Export option from Report Designer )
Let me know!!
Thanks
View 1 Replies
View Related
Aug 21, 2006
Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.
I know about @@cpu, @@io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.
Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(
I realize that in general this is a ridiculous request, but I thought I would ask anyway.
View 4 Replies
View Related
Oct 29, 2015
Can we disable a flat file connection manager in a ssis package just as how we can disable a OLE db task? When I try to disable the flat file by clicking on work offline its gets disabled which is what i expected , but when i close the package and reopen it again its again enabled , is this the way that a flat file connection manager works.
View 5 Replies
View Related
Jan 31, 2002
This one has stumped me. Hopefully somebody can help.
A while ago, I setup a trace that posted the log to the desktop. I needed to stop the trace this morning, so I went into the profiler and deleted the traces. There was a private and shared trace. Now every time I start up something that has to do with sql server, the log pops up on the desktop. I'm not sure why the trace wasn't deleted or stopped. The trace includes what program accessed sql. Whether it is EM or Query analyzer or ISQL. It gets posted in the log. Any suggestions? I need to remove this because the log fills up the drive and causes the server to crash.
Thanks
View 1 Replies
View Related
Jun 12, 2000
Can you run 6.5 traces through the SQL 7.0 profiler. If so, I am getting a login error. I do have the same db name, login, and perms.
Andy
View 1 Replies
View Related
Aug 14, 2007
Hey guys,
I need some help knowing what to look for in Profiler to troubleshoot an issue.
I've got an application that accesses a SQL Server database that has suddenly started timing out when users launch and attempt to log in, and I'm trying to find out where and why the application might be timing out (whether it's a server issue, a stored procedure or SQL query from the application that could be optimized, a table that could be truncated or archived, etc.). All I have to work with from troubleshooting the database side are a series of trace files from Profiler that were run for a total of about 5 minutes while the application was launched and then timed out. Of course, there are a whole lot of statements being issued, hundreds of tables being accessed, lots of stored procedures and even more ad-hoc queries coming straight from application source code.
So my question is, what do I need to look for in these trace files that might be a red flag to an issue? I'm no DBA, but I know that really long durations might be a tip-off. I'm only seeing these on the occasional Event:Audit Logout (which I read in another thread could potentially be very normal). Anything else that I might want to filter for?
I appreciate any help!
Mandy
View 2 Replies
View Related
Mar 20, 2008
I want to use ShowPlanXML in my traces. Information witch is given by this event is quite interesting but I dunno how to bound it with other events. For example I have SqlBatchComplete event and want to get XML plan of it but I have no idea how to do it.
View 3 Replies
View Related
Jul 20, 2007
Hi
I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .
So is this definitely a disk issue, or is there something else I need to check
thanks
P
View 1 Replies
View Related
Jan 25, 2007
Hi,
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
- <Configuration ConfiguredType="Property" Path="Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:APPSBACIARSalesforceConnectSalesforceConnect.log</ConfiguredValue>
</Configuration>
Appreciate a help.
Gulden
View 6 Replies
View Related
Sep 26, 2005
I am debating whether to go to all the trouble of setting up on-demand Profiler traces on some test servers for the developers here. Really just tracing RPC:Completed and SQL:BatchCompleted, so the developers can at least try to catch a performance problem before going to production. The question I have, though, is just how useful is this sort of information to mid- to low-level (i.e. experience) developers. One of the bigger concerns is over Java applications, which like to hide their queries behind a lot of "sp_cursorfetch" calls.
My question to the forum is if you are a developer, have you ever dreamed of having this sort of information available? How useful is it?
I am going to try to post a poll along with this, but I am not sure it will work..
View 3 Replies
View Related
Jan 4, 2006
Is there a configuration file in SQL Server where I can find basicconfiguration of the server such as instance name, home folder, datafolder, etc.
View 1 Replies
View Related
Oct 1, 2006
I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file. The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.
When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file). However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development). I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).
Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account. This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).
What is the problem here €“ surely changing the way in which the package runs should not affect the configuration file settings! Any help would be appreciated€¦
View 8 Replies
View Related
May 27, 2015
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 21, @on
[code]...
View 6 Replies
View Related
Apr 3, 2007
We are deploying our SSIS packages into different folders. For example:
Test1
est2
production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
View 1 Replies
View Related
Mar 29, 2008
Hi,
I'm sitting down again to see if I can get configuration files to work. To test, I've set up a simple package that reads a flat file and writes to a table.
I've enabled configurations by clicking the checkbox in the configurations window. I am using an xml file on the local server, and there is an absolute path defined to the file. I also added a password to the xml file so the connection will work. To this point, everything works.
However, when I go into the configuration file and edit it to point to another server, it doesn't pick it up!
That is, when I run my package, it still writes the data to the old server. It's like it's completely ignoring the configuration file and just using the OLE DB connection manager instead.
However, if I delete the OLE DB conn mgr, the package doesn't work.
Please help... this is where I got stuck before.
Thank you
View 9 Replies
View Related
May 19, 2008
Hi guys,
That's the question. I'm sure I will be encourage to use xml or other configuration types rather than INI files. But INI file has the advantage of not embedding any object property path and can be used for both packages and other applications.
Imagine a large implementation with hundreds of packages and dozens of class libraries and applications which are configured from the same INI file.
Imagine also that the production environment must support both DTS 2000 and SSIS 2005 packages as it's impossible to migrate all at once.
I've read that SSIS would support INI configuration files:
In Package Configurations Kirk says:
"...The two types of package configurations that haven't been documented yet in books online is INI and SQL configurations. INI package configurations have basically been provided for backward compatibility..."
And in BOL, in the DTSConfigurationType Enumeration description we can find that the INIFile configuration Type is also mentioned.
Anyway, a possible workaround is building a custom task with an UI that allows to assign INI keys to object properties, but I wanted to know if it's possible to avoid this.
Thanks in advance
Leandro
View 1 Replies
View Related
Jan 28, 2008
The configuration file name "C:......XMLSSIS2005_ConStr.dtsConfig" is not valid. Check the configuration file name.
The path is correct but we got this error. Please help us.
View 1 Replies
View Related
May 14, 2007
Hi,
Im loading the packages with a dutil-batch from the file-system into the sqlserver.
All the packages have xml-configuration-file.
for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET
When I try to run the packages in the sqlserver environment there is no configuration file found.
Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?
Thanks Gerd
View 5 Replies
View Related
Jan 18, 2008
I created an SSIS package that is connecting to 2 SQL Databases, one connection is using windows auth and the other database connection is using SQL Auth to connect to the database. I setup a config file to use for my ID, servername, database, and password but when I run my SSIS package it fails stating'
"[Execute SQL Task] Error: Failed to acquire connection "dbConnection". Connection may not be configured correctly or you may not have the right permissions on this connection."
If I open up my connecting dbConnection in Connection Manager the username is filled it but the password is blank, if I enter in the password and click 'test connection' it passes. I have the ProtecionLevel = EncryptSensitiveWithUserKey but the password is still 'blanked out' So my question is
1) how can I use the config file to store my id and pwd for my db connection?
2) how can I get my package to read the config file to get the ID and password for my database.
3) is there another way to do this? Would a varaiable work for this, if so how do they work?
View 1 Replies
View Related
Oct 9, 2007
Hello,
I have a package where I store connection managers in a configuration file (XML file). I updated manually the XML file to add the passwords.
When I launch the command line generated by dtexecui, everything is good:
"dtexecui /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW "
When I launch the command line with dtexec:
"dtexec /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW ",
I have the familiar error: "Failed to decrypt protected XML node "DTSassword""
I have the package level set to "EncryptSensitiveWithUserKey", but I think I don't need to update it to "EncryptSensitiveWithPassword" because it works with dtexecui.
I don't understand... What I should do?
Thanks,
Guillaume
View 7 Replies
View Related
Mar 26, 2008
I have sucessfully merged and recreated my 1st DTS package to SSIS. now I am working on the configuration file and deploying it. I have a question about config files. I have set one up to use with my package pointing to proudction data sources. If I wanted to to set one up for a development source do I just create another config file for development. How do I tell it then which config file to use when I am testing?
IE we only have one server but will have a production and a test db. Right now just have prodcution. We have just merged over from 2000 to 2005. Just wanted to make sure I understand how to point it to development when I want to to this. '
Stacy
View 7 Replies
View Related
May 28, 2007
Hello All,
In my project, developers enable configuration on the packages they develop. This means that if a project has 100 packages, each one has its own configuration file.
If our primary database server changes, I have to go an update 100 configuration files.
This defeats the purpose of having a configuration file in the first place.
I want to have a configuration file which contains common settings for all the packages in the project.
At the same time, I should have the flexibility of having individual configuration files for the project.
Is this possible and how can I do this?
View 8 Replies
View Related
Jul 6, 2007
I have a package for which I have set ProtectionLevel to DontSaveSenstive.
I connect to a SQL Database which supports mixed authentication.
To connect to this server in a flexible way, I have enabled SSIS package configuration.
I have edited my dtsconfig file with notepad and put the right password there (I am able to use management studio to connect to that database using same password).
Now when I come back to BIDS and double click on my connection manager, it doesn't read the configuration from the config file. (it just has a blank password). So it seems that BIDS doesn't read the config file.
What can I do to make BIDS read the config file.
Also, is there a way to encrypt the password (without keys) in the configuration file?
regards,
Abhishek.
View 3 Replies
View Related
Aug 30, 2006
Hi,
I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.
The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.
How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?
Any help greatly appreciated....Thank you,
Maria
View 2 Replies
View Related
Nov 26, 2007
Hi,
I'm having difficulty configuring a package to look for an xml configuration file that contains values for source/destination connection strings.
The goal is to be able to have a package and its configuration file on one file system and to be able to copy/move the file dtsx and dtsx.config between build/staging/production servers while only having to set the xml value for the source/destination configurations.
In the package, I've created an xml configuration file called zzz.config. I took the absolute path out of the location of the xml file so that I have
xml location = zzz.config
instead of
xml location = e:ssiszzz.config
I'm assuming that when it gets to the point of validating the location of that file, it starts by looking in the current directory where the package is found.
All is well as long as I execute the package through the execute package utility by double clicking on the package file.
The problem lies when configuring a job in the sql server agent, it doesn't seem to pick up the xml configuration values because its pointing to the old server and not the current server stored in the xml file. I'm assuming its because the current directory is not the package directory at this point.
How can I get around this? Do I have to move the xml config file (zzz.config) to another directory where the agent will look for?
I know I could configure this as an environment variable but this contradicts the point of having movable packages without any extra tweaks.
Any help would be appreciated.
Thanks,
Robin
View 4 Replies
View Related
Jun 1, 2006
I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.
I get error that connection exist in the configuration but not in the package
and
"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."
Any help?
View 3 Replies
View Related
Oct 17, 2006
Thanks to some earlier help from this forum, I have my package running using configuration files and Windows Authentication. However, when I try to execute the package using Sql authentication, login fails for the user i am specifying. When I check out the configuration file, the connection string does not contain the password. I added the password to the connection string, but it does not cone back. I followed the instructions found on the following site:
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html
After trying what was suggested there, I was still unsuccessful with my login attempt.
Anyone know how to set up a package to run using Sql authentication?
Thanks!
Craig
View 1 Replies
View Related
Mar 6, 2008
Is there any way I can configure the Configuration file Path.
I have an understanding that wherever I store my package but my Configuration file(XML) must be store in the place specified in the package. In my dev environment I have C Drive as such the package is in DevServerNameC:SSIS.
However, there is no C drive allocated in Prod envirnment as such package is going to rest in ProdServerName/SSIS
Now if I place my package and execute here and it will look for configuration files(XML) which are on C file path. Then I guess my package fails.
I am just wondering how can I make my package configure file path dynamic?
View 7 Replies
View Related
Nov 14, 2007
I have an SSIS package (which is already delivered to the customer, so I can't change it), but I want to switch off (disable) one of the elements. I use a dtsConfig file (XML), so I want to just send an update to that config file to switch off the element.
So, I used the Package Configurations Organizer to generate a new config file with the element switched off, and it generated the following:-
<Configuration ConfiguredType="Property" Path="PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]" ValueType="Boolean">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
So I copied these lines into my "production" config file and ran the package, but got the following warning message:-
Warning: 0x80012017 at ImportData: The package path referenced an object that cannot be found: "PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Can anyone explain why? Is there some issue with having spaces in the Path name perhaps? Is there some syntax to get around it? Why did it generate a Path that is wrong?
Thanks
- AA
View 5 Replies
View Related
