SQL Log File Size is Growing

Posted by Vuyiswamb under Error and Solution on 6/23/2010 | Views : 3176 | Status : [Member] [MVP] [Administrator] | Replies : 2

One of users tried to make a backup on the database from the system and she received the following strange Error

Exception caught in: ExecuteStoredProc: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 44621430784 additional free bytes, while only 36018462720 bytes are available. Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally.

but i know i have 300 gig Free space on the c: Drive of that server. But when i get into the server , i realized that one of the database log files has grown to a size that should be impossible for it. it was 80 GIG, i used the following sql to truncate the log file

            DBCC SHRINKFILE('LogFileName', 1)

and to find the log file name , use the following Query

select name from sys.database_files where type = 1

Thank you

Thank you for posting at Dotnetfunda


Posted by: Abhi2434 on: 6/23/2010 [Member] [Microsoft_MVP] [MVP] Silver


This is quite general case to a server. Log files in server generally grows over time. So you need scheduled backup service (maybe in midnight or the time when server is idle) which will truncate the transactioon logs of the server.
You are right
dbcc shrinkfile and dbcc shrinkdatabase

You can read more from :


Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/24/2010 [Member] [MVP] Silver

Dear Vuyiswamb

The Exception seems to be RESTORE Issue On the Target Drive.

How to analyse BACKUP file size and Target space

If you trying to restore a Database. You have to focus on the following areas

1. Ensure the Backup file(.BAK) is readable/accessable by the Operating System

Your Backup file Path : D:\Vuyiswamb\Database1.Bak

You trying to Restore On C:\ Drive

Just run the statement give below to examine the backup file
The above statement will examine and return One line for Each Files (Data and Log).

If the statement returns any Err then Your Backup File is not correct. If the statement returns any results then...

Examine the following way... How much you need to RESTORE the backup @ C:\ drive.

In that Result. You will have one column "SIZE", That column will have Actual Size of Data file and Log file in BYTES.

Just convert the BYTES into MB.
Data File - 434110464
Log File - 153288704

1. Just add the both value
434110464 + 153288704 = 587399168 Bytes.

2. Convert the total Bytes to MB
(587399168/1024.) / 1024. = 560.1875 MB

3. So, You need 560.1875 MB free space to RESTORE the Backup @ C:\ drive

We can schedule Maintenance Plan for Reclaiming the allocated but Unused spaces using SHRINKFILE as Abhi2434 told.

Conclusion :
Whenever we to try to RESTORE a database backup, We should not considered about only the .BAK file size alone.

We have to analyse the actual size.


iLink Multitech Solutions

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response