The transaction log for database is full. To find out why space in the log cannot be reused
OR
Could not allocate space for object because the filegroup is full
Reema:
Hello DBA it seems our application not working today. We got some error it looks like some problem with the database.
DBA:
This error mostly occurs because of insufficient space on the drive.
Have you checked the drive space?
Reema:
Yeah, I have checked the drive space in a server –> my computer. It looks I have plenty of space on the server.
DBA:
Can you check the database auto growth setting in the database?
Reema:
I have 30 databases on my server. How can I check it?
DBA:
Check the error log and find which database throwing error and right click the database check the auto growth setting.
Reema:
It seems I am not principally SQL DBA. Can you tell me the steps?
The conversion goes like this.
Use of this script:
- When you face an insufficient disk space problem you can add your drive letter to the “WHERE CLAUSE” and you can find which file is using more disk space.
“WHERE A.PHYSICAL_NAME LIKE ‘E%’”
- In some case, The auto growth setting will be restricted. In this case You can easily find the auto growth setting for all databases and you can change the setting for the database (DB which is causing a problem).
- In some case, The database size is big but it will not use as much space but you will get an insufficient disk space error. In this case you can easily identify the database by using second script and you can reclaim the space.
Check the drive free space:
XP_FIXEDDRIVES
For all Databases:
The following script will show the database current size, auto growth setting and file location.
SELECT DB_NAME(A.DATABASE_ID) AS DBNAME,A.NAME AS FILENAME , SIZE/128.0 AS CURRENTSIZE_MB, B.RECOVERY_MODEL_DESC,A.TYPE_DESC , CASE WHEN IS_PERCENT_GROWTH = 0 THEN LTRIM(STR(A.GROWTH * 8.0 / 1024,10,1)) + ' MB, ' ELSE 'BY ' + CAST(A.GROWTH AS VARCHAR) + ' PERCENT, 'END + CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH' ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB' END AS AUTOGROW,A.PHYSICAL_NAME FROM SYS.MASTER_FILES A JOIN SYS.DATABASES B ON A.DATABASE_ID =B.DATABASE_ID --WHERE A.PHYSICAL_NAME LIKE 'E%' -- AND A.FILE_ID =2 ORDER BY A.SIZE/128.0 DESC
For a particular Database:
The following script will give about the particular database current size, used space; free space and auto grow setting.
SELECT DB_NAME() AS DBNAME, NAME AS FILENAME, SIZE/128.0 AS CURRENTSIZE_MB, SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0) AS USEDSPACE_MB, SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB, PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC, CASE WHEN IS_PERCENT_GROWTH = 0 THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, ' ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END + CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH' ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB' END AS AUTO_GROW FROM SYS.MASTER_FILES WHERE DATABASE_ID = DB_ID()
In most cases, The log file gets bigger when you are not maintained in your database properly. That is choosing the right recovery model and backup types it will help to minimize the log file size.
https://sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/
Bear in mind: Ask your customer about their RPO and RTO and scheduled the log backup regularly.
Additional Reading:
https://sqlserverblogforum.com/2011/04/shrinking-the-log-file-script-for-sql-server-database/
