SQL Server Performance Forum – Threads Archive

URGENT!!!

does anyone know where the "built-in" reports from sql 2005 are stored?
Specifically I need to know where SQL stores its trace file info for the default trace!
I am wanting to look back at backups of those to see schema changes. We reboot the servers every week and need to know what happened to a table last weeek! Mike Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

– these tracefiles can be found in the …Log folder.
– [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.***MSSQLServer
key "DefaultLog"="D:\MSSQL.1\MSSQL\Log" points to the default log location.

Thanks alzdba<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The fear of the Lord is the beginning of knowledge, but fools despise wisdom and instruction." Proverbs 1:7

Offcourse if you want a swift and clear view regarding schema changes, maybe setting up a ddltrigger is the less bumpy road.
e.g.
use yourdb
go
/****** Object: Table [dbo].[T_Audit_DDL_Log] Script Date: 01/17/2006 10:11:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Audit_DDL_Log](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoginUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OriginalLoginUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_T_Audit_DDL_Log_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO GO
/****** Object: DdlTrigger [ddlDatabaseTriggerLog] Script Date: 01/17/2006 10:06:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO CREATE TRIGGER [ddlDatabaseTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON; DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname; SET @data = EVENTDATA();
SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘sysname’);
SET @schema = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘sysname’);
SET @object = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘sysname’) — IF @object IS NOT NULL
— PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema + ‘.’ + @object;
— ELSE
— PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema; — IF @eventType IS NULL
— PRINT CONVERT(nvarchar(max), @data); INSERT [dbo].[T_Audit_DDL_Log]
(
[PostTime],
[DatabaseUser],
[LoginUser],
[OriginalLoginUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
CONVERT(sysname, SUSER_SNAME()),
CONVERT(sysname, ORIGINAL_LOGIN()),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(max)’),
@data
);
END;

Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Roji. P. Thomas Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a> <hr height="1" noshade id="quote"></blockquote id="quote"> You advised him/her what I wanted to advise [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Roji. P. Thomas Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> Guessing from his signature, Michael seems to be a "him". [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> That’s an amazing skill. I will forward you a set of signatures. Will you please…? [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

Just pass them on! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Roji. P. Thomas Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> I see only User name [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Frank, I know at least one female Michael. And in Enid Blyton’s Famous Five series, Georgina is called George.

If you mean this famous androgynous person, I agree with you. …and Timmy was the dog… —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Not sure which famous androgynous person you’re referring to? My acquaintance is a fairly private person.

In that case you can ignore my guessing. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

I am a guy for crying out loud!<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> and yes.. I will look for a better title next time.. got your attention though didnt it<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The fear of the Lord is the beginning of knowledge, but fools despise wisdom and instruction." Proverbs 1:7

Ok Mike but is your surname Jackson? Frank wants to know.

You are not alone here, as I was being guessed as a lady previously as my name sounds like that in Indian history[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MikeEBS I am a guy for crying out loud!<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ……<hr height="1" noshade id="quote"></blockquote id="quote">

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya You are not alone here, as I was being guessed as a lady previously as my name sounds like that in Indian history[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> ……<hr height="1" noshade id="quote"></blockquote id="quote"> <hr height="1" noshade id="quote"></blockquote id="quote"> Yes. First I thought you are woman till you were referred as "He" [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Are you disappointed after that [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], just kidding. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

Thomas, No… my surname is not Jackson.. You think he would know what a database was? Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Not sure about that. Might depend on what is stored in the db. Probably a very special sort of images might catch his interest. [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

While Michael may be excused for his ignorance, I’m abhorred that Frank would ever suggest to store images in a database!

Why not?
It depends on the situation. I have 1 db at about ~12 GB storing almost nothing else but BLOBs of all sizes. I am the only one updating it. The rest of the users are simply accessing and reading. Much easier to handle in terms of consistencies in just one system (db) rather than two (db and filesystem). One backup and security permission strategy. Works like a charm. But this is my situation. There are others as well where it really depends on the single factor as (size of BLOBS, users, concurrency, activity…) —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

I thought we were joking on Tuesday here?

Oops sorry, didn’t catch your joke. [:I] —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>