Post reply

database log file

  • December 4, 2012 at 6:30 pm

    #393740

    If a database is setup to readonly and in simple recovery mode.

    Will the log file grow or not?

    thanks

  • December 4, 2012 at 6:48 pm

    #1565550

    It shouldn't since you can't run insert, update, or delete statements. In addition you can't update the statistics or reorganize or rebuild indexes.

  • December 5, 2012 at 3:21 am

    #1565644

    sqlfriends (12/4/2012)

    If a database is setup to readonly.

    Will the log file grow or not?

    thanks

    NO

    sqlfriends (12/4/2012)

    If a database is setup in simple recovery mode.

    Will the log file grow or not?

    thanks

    Yes

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 5, 2012 at 4:17 pm

    #1565961

    So if a database is set to read only , and by just querying the database, it won't create any log transactions, so no affect on log file, is that correct?

    Thanks,

  • December 5, 2012 at 4:30 pm

    #1565964

    sqlfriends (12/5/2012)

    So if a database is set to read only , and by just querying the database, it won't create any log transactions, so no affect on log file, is that correct?

    Thanks,

    That is correct. You can confirm that using fn_dblog(null,null).

    USE [master]

    GO

    ALTER DATABASE [DBA_Rep] SET READ_ONLY WITH ROLLBACK IMMEDIATE

    GO

    SELECT * FROM fn_dblog(null,null)

    Returned 3 transaction log records in my development environment

    SELECT * FROM dbo.Backup_History

    SELECT * FROM fn_dblog(null,null)

    Returned the same 3 transaction log records

  • December 6, 2012 at 1:52 am

    #1566045

    sqlfriends (12/5/2012)

    So if a database is set to read only , and by just querying the database, it won't create any log transactions, so no affect on log file, is that correct?

    Selects are never logged, in any recovery model in any database setting. Database changes are what are logged. A read only database can't be changed and hence won't get any log records.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 6, 2012 at 12:29 pm

    #1566313

    Thanks much, that helps

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

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