Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Blog Forum

My name is Muthukkumaran Kaliyamoorthy and I am living in India (Chennai). I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server, and I’m specialized in Administration and Performance tuning.

Find the database free space sql server The transaction log for database is full

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:

  1. 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%’”

 

  1. 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).

 

  1. 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.
http://www.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:

http://www.sqlserverblogforum.com/2011/04/shrinking-the-log-file-script-for-sql-server-database/

http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

 

Comments

Leave a comment on the original post [www.sqlserverblogforum.com, opens in a new window]

Loading comments...