SQL Log File Size is Growing

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

[Resolved]

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)
     		BACKUP LOG Database_Name WITH TRUNCATE_ONLY
            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
[Administrator]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	Yes. 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 : http://msdn.microsoft.com/en-us/library/aa174524 www.abhisheksur.com Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

Yes.

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 :
http://msdn.microsoft.com/en-us/library/aa174524

www.abhisheksur.com

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

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

0	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 RESTORE FILELISTONLY FROM DISK='D:\Vuyiswamb\Database1.Bak' 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. i.e: 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. Cheers Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

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

RESTORE FILELISTONLY FROM DISK='D:\Vuyiswamb\Database1.Bak'

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.
i.e:
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.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Latest Posts