Can logs get very big in the simple recovery database?

  • I have database in express SQL 2008 in th simple recovery mode, can logs grow in this db? If yes, how can I protected?

  • Krasavita (5/5/2010)


    I have database in express SQL 2008 in th simple recovery mode, can logs grow in this db? If yes, how can I protected?

    hi,

    How to Shrink

    dbcc sqlperf(logspace)

    go

    sp_helpdb

    -- I

    BACKUP LOG AdActivity_prod WITH NO_LOG

    go

    -- II

    DBCC SHRINKDATABASE (AdActivity_prod, 10 )

    go

    if still you face higher usage try using

    -- backup log tempdb WITH NO_LOG

    -- dbcc shrinkfile (templog,0)

    -- dbcc shrinkfile (tempdev,0)

    The above code(backup log tempdb WITH NO_LOG) will work for Sql 2008

    How to Protect

    Move the tempdb database to a different disk location.

    Make use of the script attached below :

    -- Determine the logical file names of the tempdb database and their current location on the disk.

    SELECT name, physical_name AS CurrentLocation

    FROM sys.master_files

    WHERE database_id = DB_ID(N'tempdb');

    GO

    --Change the location of each file by using ALTER DATABASE.

    USE master;

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\tempdb.mdf');

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\templog.ldf');

    GO

    --Stop and restart the instance of SQL Server.

    --Verify the file change.

    SELECT name, physical_name AS CurrentLocation, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID(N'tempdb');

    --Delete the tempdb.mdf and templog.ldf files from the original location

    Hope this helps;

  • I am littele confused,but at this time I have 5474.63 MB for temp if I re start sql server instanse, I thought temp db re creates again and size would be down, right?

  • It would be nice if you were more descriptive in asking your question.

    Yes, a database using the SIMPLE recovery model can still have its log file grow. it is dependent on how much data is being inserted.updated/deleted in a single transaction and how many concurrent transactions are running.

    If the database is using the SIMPLE recovery model, BACKUP LOG will not work, even if you are doing TRUNCATE_ONLY. Also, realize that that is being depreciated and may not be available in future versions of SQL Server (talking about BACKUP LOG WITH TRUNCATE_ONLY, not BACKUP LOG itself).

  • Lynn Pettis (5/5/2010)


    If the database is using the SIMPLE recovery model, BACKUP LOG will not work, even if you are doing TRUNCATE_ONLY. Also, realize that that is being depreciated and may not be available in future versions of SQL Server (talking about BACKUP LOG WITH TRUNCATE_ONLY, not BACKUP LOG itself).

    Since this is SQL Server 2008 forum - you should know that the above command will not work. It is not being deprecated - it has been deprecated in this version 😉

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

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