URGENT!!! | SQL Server Performance Forums

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=’:)‘ /><br /><br />Michael <br />MCDBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />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=’:)‘ />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br />Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You advised him/her what I wanted to advise [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br />Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You advised him/her what I wanted to advise [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Guessing from his signature, Michael seems to be a "him". [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Guessing from his signature, Michael seems to be a "him". [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />That’s an amazing skill. I will forward you a set of signatures. Will you please…? [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
Just pass them on! [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br />Mike, Next time please consider using a meaningful Title [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You advised him/her what I wanted to advise [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Guessing from his signature, Michael seems to be a "him". [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I see only User name [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />Madhivanan<br /><br />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=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />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=’:)‘ /><br /><br />Michael <br />MCDBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />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=’:D‘ />].<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MikeEBS</i><br /><br />I am a guy for crying out loud!<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> ……<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />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=’:D‘ />].<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MikeEBS</i><br /><br />I am a guy for crying out loud!<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> ……<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. First I thought you are woman till you were referred as "He" [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Are you disappointed after that [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />], just kidding.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></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=’:D‘ />] 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=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |