Re: How to capture SQL statement messages in DTS log file
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Tue, 18 Sep 2007 17:00:38 -0400
Ellen,
To capture the statistics date, look at the STATS_DATE function in the Books
Online.
The server knows, since it can show you that on the query plans that
statistics are out of date. You might find the following article helpful in
seeing what is going on with statistics updates.
http://support.microsoft.com/kb/195565
Yes, SQL Server 2000 and 2005 are radically different, since 2005 exposes
much information that was hard or impossible to get in 2000. However, if
you have auto update for statistics you may be less interested in the time
the statistics were last updated than in other factors, such as
fragmentation.
In both SQL Server 2000 and 2005 you can run DBCC SHOWCONTIG (you need to
read about it) to collect indexes that need defragmentation. Statistics may
be updated during defragmentation, depending on the command you use.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
In SQL Server 2005 there is a view sys.dm_db_index_physical_stats that will
give you the information more easily.
FWIW,
RLF
"Ellen" <DBAEllen@xxxxxxxxxxxxxxxx> wrote in message
news:319BC75E-8EA3-4623-8E99-70FB3D10787E@xxxxxxxxxxxxxxxx
Russell:
Thank you for the suggestion. It seems like 'PRINT' should work. I think
there must be something that needs set. I am rather new to SQL Server
having
come from the Oracle & Informix worlds. Another option would be to check
a
system table for a date column to check the last time a table or index had
stats analysis run. Would you by chance know which table or view it would
be? I have several SQL Server 2000s and a couple of SQL Server 2005
servers
that I need to put jobs in place to do weekly update stats. It seems like
there were major system changes between the two and all the system
tables/views have changed.
Again , thank you for your assistance.
Ellen
--
Thank you for sharing your knowledge
"Russell Fields" wrote:
Ellen,
I have used that successfully in the past, but maybe it was not with the
PRINT statement, but some other executables. How about a simple
alternate
suggestion. Create a trace table youself in insert the data to it, e.g.:
CREATE TABLE TroubleShoot
(ID INT IDENTITY,
TraceString NVARCHAR(255),
EventTime DATETIME DEFAULT GETDATE(),
SPID INT DEFAULT @@SPID)
Then instead of PRINT in your DTS package, do the following:
INSERT INTO TroubleShoot (TraceString)
VALUES ('Here is my formatted trace string')
Afterward, you can select them and examine the results.
RLF
"Ellen" <DBAEllen@xxxxxxxxxxxxxxxx> wrote in message
news:C807B342-A843-4B33-B0AE-81B55F675612@xxxxxxxxxxxxxxxx
I also thought that should do it but it does not.
The only thing written to the log is in the text I included earlier.
I've tried print statements and select statements and I've had the log
at
the package level and at the step level with the same output showing
up.
I
am hoping someone knows what else needs to be done to get the PRINT
statements to write to the log.
Thanks for trying.
Ellen
--
Thank you for sharing your knowledge
"Russell Fields" wrote:
Ellen,
In the SQL Agent Job properties, go to the step properties for the
step
that
runs your DTS package. In the Advanced tab on the Job Step you can
specify
either an Output File or to Log to Table. (The table is
msdb.dbo.sysjobstepslogs.)
Either approach should capture your console output through the PRINT
statements.
RLF
"Ellen" <DBAEllen@xxxxxxxxxxxxxxxx> wrote in message
news:3E08CDBC-E2FE-43BD-B74D-06E24BE05A75@xxxxxxxxxxxxxxxx
I appreciate your answer; but the issue I need to address is that
this
"job"
is a DTS package that is run through the job scheduler. The only
log I
am
getting does not "capture" the print message. Is there a way to
capture
that
print message in a log for a DTS package step. Adding the code
below
[or
similar code] will run without error but the log file only has the
output
below. Is there a statement I need to set at the beginning of the
SQL
statements like 'echo on' or something?
Thank you for your assistance.
Ellen
-------------- the log ------------------------------------
The execution of the following DTS Package succeeded:
Package Name: Update Statistics on DYNAMICS
Package Description: Update statistics for DYNAMICS User Tables
Package ID: {46569BFF-FD34-42C1-AFE9-74E4E4A0EF82}
Package Version: {1888A3F0-140B-4751-B545-A0D4B7209298}
Package Execution Lineage: {46683000-1F33-4071-A82B-91DBFEA2A652}
Executed On: SQLDY01
Executed By: ellen
Execution Started: 9/18/2007 9:55:38 AM
Execution Completed: 9/18/2007 9:55:38 AM
Total Execution Time: 0.172 seconds
Package Steps execution information:
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 9/18/2007 9:55:38 AM
Step Execution Completed: 9/18/2007 9:55:38 AM
Total Step Execution Time: 0.109 seconds
Progress count in Step: 0
--
Thank you for sharing your knowledge
""Peter YangMSFT]"" wrote:
Hello Ellen,
Actually, when server run the command 'update statistics
<tablename>'
successfully, it doesn't return anything.
You could see "Command(s) completed successfully" message in Query
Analyzer
but you shall not see this from other applications such as OSQL etc
because
the message is actaully generated by QA itself.
You could check "@@error" variable for the success of each command
if
you
don't get any errors. You can view the text associated with an
@@ERROR
error number in sysmessages system table. For example, you could
use
the
following script to do check the error of update statitics command.
=====
declare @tablename varchar(50)
select @tablename = 'tbl1'
declare @query as varchar(200)
select @query= 'update statistics ' + @tablename
exec (@query)
IF @@ERROR <> 0
BEGIN
PRINT N'An error occurred while updateing statistics of
table
'+
@tablename;
RETURN 99;
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT N'update statistics succeeds for table '+ @tablename;
RETURN 0;
END;
If you have any further questions or comments on this issue, please
feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to
reach
the
most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or
complex
project analysis and dump analysis issues. Issues of this nature
are
best
handled working with a dedicated Microsoft Support Engineer by
contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
.
- Follow-Ups:
- References:
- RE: How to capture SQL statement messages in DTS log file
- From: "Peter YangMSFT]"
- RE: How to capture SQL statement messages in DTS log file
- From: Ellen
- Re: How to capture SQL statement messages in DTS log file
- From: Russell Fields
- Re: How to capture SQL statement messages in DTS log file
- From: Ellen
- Re: How to capture SQL statement messages in DTS log file
- From: Russell Fields
- Re: How to capture SQL statement messages in DTS log file
- From: Ellen
- RE: How to capture SQL statement messages in DTS log file
- Prev by Date: Re: How to capture SQL statement messages in DTS log file
- Next by Date: Re: How to capture SQL statement messages in DTS log file
- Previous by thread: Re: How to capture SQL statement messages in DTS log file
- Next by thread: Re: How to capture SQL statement messages in DTS log file
- Index(es):
Relevant Pages
|
