SQL Server Performance

URGENT!!!

Discussion in 'SQL Server 2005 Reporting Services' started by MichaelB, Mar 14, 2007.

Page 1 of 2

  1. MichaelB Member

    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
    MichaelB, Mar 14, 2007
    #2

  2. alzdba Member

    - 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.
    alzdba, Mar 16, 2007
    #2

  3. MichaelB Member

    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
    MichaelB, Apr 12, 2007
    #2

  4. alzdba Member

    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;
    alzdba, Apr 12, 2007
    #2

  5. Roji. P. Thomas New Member

    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 />
    Roji. P. Thomas, Apr 13, 2007
    #2

  6. Madhivanan Moderator

    <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
    Madhivanan, Apr 13, 2007
    #2

  7. FrankKalis Moderator

    <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>
    FrankKalis, Apr 13, 2007
    #2

  8. Roji. P. Thomas New Member

    <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 />
    Roji. P. Thomas, Apr 13, 2007
    #2

  9. FrankKalis Moderator

    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>
    FrankKalis, Apr 13, 2007
    #2

  10. Madhivanan Moderator

    <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
    Madhivanan, Apr 13, 2007
    #2

  11. Adriaan New Member

    Frank,

    I know at least one female Michael. And in Enid Blyton's Famous Five series, Georgina is called George.
    Adriaan, Apr 13, 2007
    #2

  12. FrankKalis Moderator

    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
    FrankKalis, Apr 13, 2007
    #2

  13. Adriaan New Member

    Not sure which famous androgynous person you're referring to? My acquaintance is a fairly private person.
    Adriaan, Apr 13, 2007
    #2

  14. FrankKalis Moderator

    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>
    FrankKalis, Apr 13, 2007
    #2

  15. MichaelB Member

    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
    MichaelB, Apr 13, 2007
    #2

  16. thomas New Member

    Ok Mike but is your surname Jackson? Frank wants to know.
    thomas, Apr 13, 2007
    #2

  17. satya Moderator

    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">
    satya, Apr 14, 2007
    #2

  18. Madhivanan Moderator

    <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
    Madhivanan, Apr 15, 2007
    #2

  19. satya Moderator

    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>
    satya, Apr 16, 2007
    #2

  20. MichaelB Member

    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
    MichaelB, Apr 17, 2007
    #2

  21. FrankKalis Moderator

    [<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>
    FrankKalis, Apr 17, 2007
    #2

  22. Adriaan New Member

    While Michael may be excused for his ignorance, I'm abhorred that Frank would ever suggest to store images in a database!
    Adriaan, Apr 17, 2007
    #2

  23. FrankKalis Moderator

    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
    FrankKalis, Apr 17, 2007
    #2

  24. Adriaan New Member

    I thought we were joking on Tuesday here?
    Adriaan, Apr 17, 2007
    #2

  25. FrankKalis Moderator

    Oops sorry, didn't catch your joke. [:I]

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Apr 17, 2007
    #2
Page 1 of 2

Share This Page