Post reply

Transaction Log

  • June 12, 2009 at 4:42 am

    #210544

    Hi,

    I'm new to sql server and I'm looking for a command to fill up the transaction log of the adventureworks db so that I can work out how to resolve issues when this happens.

    Any help much appreciated.

    Thanks

  • June 12, 2009 at 4:49 am

    #1008503

    Then why not run INSERT and DELETE statement in a loop?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 12, 2009 at 4:51 am

    #1008504

    Frank,

    Can you start me off with the syntax?

  • June 12, 2009 at 4:57 am

    #1008507

    Maybe something like this will work:

    CREATE TABLE #t (c1 int);

    DECLARE @i int;

    SET @i = 1;

    WHILE @i <= 10 --adjust this

    BEGIN

    INSERT INTO #t SELECT @i;

    UPDATE #t SET c1 = @i;

    DELETE #t WHERE c1 = @i;

    SET @i = @i + 1;

    END

    SELECT * FROM #t;

    DROP TABLE #t;

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 12, 2009 at 4:58 am

    #1008509

    Oops, apparently you would want to repleace #t with a permanent table in the AdventureWorks database. 🙂

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 12, 2009 at 5:05 am

    #1008513

    Thanks Frank, I'll give this a go.

  • June 12, 2009 at 5:08 am

    #1008515

    Sorry before I do this, can I replace the #t with person.address?

  • June 13, 2009 at 9:35 am

    #1009087

    you can. however, since the structure of person.address is different from what was shown in the example, you'll need to modify the queries for inserting.

    You can also create a permanent table, just remove the # from the sample table name.

    Alternatively, you may want to limit your transaction log to, say 1 MB without autogrowth and then run that example. your logs will get filled with much lesser number of loops.


    Pradeep Singh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply