:::: MENU ::::

Posts Categorized / Transaction log files

  • Oct 04 / 2013
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, Transaction log files

Get total size of log file along with used space percentage for each SQL Server database

Always there exists a single log file for each SQL Server database. Log file keeps growing when in full or bulk logged recovery model. DBAs implement strategies to manage the log file size. There are two aspects of log file size.

  • One is log file size that it captures on disk and is shown at OS level.
  • Second aspect relates to filled part of over all log file.

Suppose there is a log file with size 20 GB on disk then it is quite possible that it is filled with only 1 GB log and rest of 19 GB is empty. SQL Server will keep populating the remaining part unless it is full and then will claim more size according to file growth settings.
It is a basic requirement to have an idea of log file and its used space. Following is a very simple way to get this information. You may also insert the information into a table on daily or weekly basis and may process alerts for these.

DBCC SQLPERF (LOGSPACE)
GO

Get total size of log file along with used space percentage for each SQL Server database

As a result we have Database name, total log file size as well as the filled log space percentage. Last column status is always zero.

  • Dec 13 / 2012
  • 0
dbDigger, Disaster Recovery, Maintenance plans, SQL Server Agent scheduled Jobs, SQL Server Error messages, Transaction log files

Log backups fail after changing the SIMPLE recovery model

Recently a scenario was shared with me where maintenance plan was failed to create the log backups. Actually recovery model of DB was set to SIMPLE to prevent the log file population for some log intensive bulk operations. After completing the operations recovery model was put back to FULL. Every thing was OK till this point but maintenance plan job began to failed later for creating log backups of that database with following error

BACKUP LOG cannot be performed because there is no current database backup.

The reason for error is that after changing the recovery model of DB from SIMPLE to BULK LOGGED or FULL, we have to create a full or differential backup before going for log backup.
Solution to avoid such error is simple that we have to go in following sequence

  • Change recovery model to simple
  • Complete the required operations
  • Change back to FULL or BULK LOGGED recovery model
  • Create FULL or DIFFERENTIAL backup
  • Successfully create log backups
  • Here is a quick demo to simulate the above steps

    USE [master]  
     GO  
     -- Set the DB to simple recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY SIMPLE  
     GO   
     -- Set the DB to full recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY FULL  
     GO   
     -- Try to Backup the log, it would fail with error  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
     -- Now Create full or differential backup of database  
     Backup DATABASE DBDIgger   
     to Disk = 'C:DBD-FullBackup.bak'   
     GO  
     -- Try to Backup the log, it would be OK  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
    
    • Dec 10 / 2011
    • 0
    DBA Interview questions, dbDigger, SQL Server Training, Storage, Transaction log files

    Major types of SQL Server files

    SQL Server databases have three types of files:

    Primary data files

    Primary data file is the starting point of the database. It points towards other files in the database. Every database has one primary data file. Recommended file extension for primary data file is .mdf.

    Secondary data files

    All data files other than the primary data file in a database are secondary data files. These are optional and may be added for further requirements. Recommended extension for secondary data files is .ndf.

    Log files

    Transaction log files hold all the log information used to recover the database and individual transactions.  At least one log file is required for each database. Recovery models of the database affect the growth of log files. Recommended extension for log files is .ldf.
    It is notable that SQL Server does not enforce the recommended extensions, but for proper and standard environment these should not be changed.

    • Jun 18 / 2009
    • 0
    DBA best practices, dbDigger, Performance Tunning and Optimization, Publications of Atif Shehzad on MSSQLTips.com, Transaction log files

    Move SQL Server transaction log files to a different location via T-SQL and SSMS

    My article related to Move SQL Server transaction log files to a different location via T-SQL and SSMS is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Move SQL Server transaction log files to a different location via T-SQL and SSMS

    • May 23 / 2009
    • 0
    dbDigger, Transaction log files

    The primary data or log file cannot be removed from a database.

    The error message
    Msg 5009, Level 16, State 9, Line 1
    The primary data or log file cannot be removed from a database.
    is run time error of severity level 16 and can occur on any version of SQL Server. It appears when you try to remove the primary data or log file from a database. This operation is not allowed. You can not remove the primary data or log file from a database.

    • Mar 30 / 2009
    • 1
    DBCC Commands, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks, Transaction log files

    How to truncate the transactional log

    In one of previous posts about Dealing the growth of Transactional Log Files i discussed briefly about function of transactional log files in SQL Server and also there were some prevention measures to prevent growth of transactional log file on large amount.
    There may be situation that we have to get rid of grown file. In that case obvious work around is to truncate the transactional log. To truncate the transaction log for AdventureWorks database

    ALTER DATABASE AdvetureWorks  
     SET recovery SIMPLE  
     GO  
     DBCC shrinkfile (LogFileNameofAdventureWorks)  
     GO  
     ALTER DATABASE AdventureWorks  
     SET recovery FULL  
     GO  
    

    As in case of simple recovery mode SQL Server transaction log is not maintained for that database. So the transactional log will be dropped for database when recovery model is set to simple.
    It is important to mention that on going transactions should be considered before changing recovery models.

    Pages:12
    Consult us to explore the Databases. Contact us