There are many reasons which cause the SQL Server Log file to grow to unbelievable size i.e., the space available in the Disk.
1. whenever we have not scheduled a Log_backup on the server the log file size grows bigger and occupies the disk on the specific drive.
2. If we have any repplication setup. Then we will be have log file growth.
3. Due to long running transaction also that causes the Log file grows.
All the transactions that are performed on the SQL are done at the log files, after a commit transaction i.e.., from code or the SQL Server check point the data is updated to the Data file of the database.
To Check the Growth of the Database Log files.
DBCC SQLPERF(LOGSPACE)
The Log Size (MB) column shows the size of the log files
The Log Space Used(%) column shows how much of the log space is used by the SQL Server Log Files.
Example:
In some environment we will be able to see the below
Log Size (MB): 43562.19
Log Space Used: 1000.45
When ever we take the Standard reports on the SQL Server we can see the following.
Total Space Use: 43562.19 MB
Data File Space Use: 847.88 MB
Transaction LogUse: 42714.31 MB
By the above figures we can see that only 188 MB is used by the log File. The The 42 GB space is empty. So we can release the space so that it can be accommodated to the disk. It can be used by the SQL Server for usage to store the data on the disk.
Comments