Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : Microsoft SQL Log File 210GB And Growing

[JustinK101]

Hello, we are running Microsoft SQL 2005 Express edition (9.0.32).

Recently I just noticed that the database log file of our main database is HUGE. The database data file is only 50MB and the log file is 210GB.

Any idea what is causing this? Seems to be getting bigger with time, in the last 7 days seems to have grown by 100GB. I noticed the following settings under the database:

Autogrowth: By 15 percent, unrestricted growth

Does that seem right? Thanks.

[waveweb]

It seems that you are not making backups to your sql database and logs. If you make a backup of your transaction log it will reduce the size, since it will truncate it and comit all transactions.

You should set the autogrowth depending on your database size, you will want to make the autogrowth if the database size increase, but you should avoid it, since when the database autogrowths it will affect the server performance.

I hope this helps.

[JustinK101]

Yeah, I do backups of the data, via a script I found. We are running Microsoft SQL Express which does not include the ability to do maten plans though. How do you backup the log file, not sure I follow.

Thanks for the help.

[JustinK101]

Here is the script we use:

[sql]
BACKUP DATABASE [Bootstrap] TO [ThumbDrive] WITH NOFORMAT, INIT, NAME = N'Bootstrap-Full-Database-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Bootstrap' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Bootstrap' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Bootstrap'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [ThumbDrive] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
[/sql]

[waveweb]

In a SQL database you have the data files and the log files. The data files have the data itself and the logs have the modifications that you have done to your SQL

You have to make a backup of your transaction log, check this article that explains how you can make it http://msdn2.microsoft.com/en-us/library/ms179478.aspx

In order to avoid the log to get bigger you can change it to simple recover mode, but it has some disadvantages, check this artcile here: http://support.microsoft.com/kb/873235

You should have a plan to make backups of your database and regular log backups to your transaction log.

A common pratice is to have full backups at friday and log backups during the week days.

Your script dosen't make log backups.

[JustinK101]

Ok I am trying to figure out how to modify the T-SQL script I posted above to create another backup of the transaction log and store that as well. I don't want the transaction log to override the data log though. I assume I need to create another backup device profile.

Can you perhaps provide the T-SQL script code?
here is reference I am trying to figure out: http://msdn2.microsoft.com/en-us/library/ms186865.aspx

[JustinK101]

Also doing the manaul backup, from the guide you posted, I cant even do. I dont have enough free space on any of the disks, 210GB is huge. Can I just truncate all that transaction log?

[FHDave]

You should be able to simply shrink the transaction log.

[waveweb]

well i am not sure if a shrink is going to do something with a 210GB log file, you will have to truncate it first and then you can shrink it, but as always a backup before it is always recomended.

Are you able to map a network drive from another server and backup the log file for that drive?

I am not sure what you mean with the log file to override the data log, since they are same, but assuming you mean data file, well it won't override it for sure, it will just commit the transactions.

I don't have currently a script for SQL Express, since i use at the moment backup exec software

[JustinK101]

What backup exec software do you use? We would consider buying a third party application if I could have more control.

Currently there is just one backup that overides each night. I would like the ability to say, back up 5 nights, then after 5 nights override starting with the oldest, things like that.

[JustinK101]

Is there any way to just abandon my 210GB transaction log, and start a new file? That thing is a monster.

[waveweb]

yes, you can delete the file and SQL will create a new log, but this is not advised at all, since you might have problems.

you can also just make the truncate of the log and then shrink it, it will solve your problem. If you try to shrink now, probably nothing will happen.

I use Veritas Backup Exec, but for you it is not the right choice, i have it because i have to backup several applications like Lotus, Sharepoint, SAP, Windows 2003 and Windows 2000 servers, etc but in your case, a good T-SQL script will just work great.

I will install soon a SQL Express and at that time i will create a script myself, but i have this just planned to the beggining of the next year.

After you solve the problem with your transaction log, you will have more space to have more backups right? I will prefer to have less backups but be able to restore older backups.

[RBBOT]

yes, you can delete the file and SQL will create a new log, but this is not advised at all, since you might have problems.

Replece "might" with "will definitely". The log is an integral part of the database, its not like a web log that just records what has happened, and you cannot delete it.

You need to shrink the log which you can do by right clicking on the database name in management studio and choose to shrink data files.

[waveweb]

Originally Posted by RBBOT

Replece "might" with "will definitely". The log is an integral part of the database, its not like a web log that just records what has happened, and you cannot delete it.

it seems that you are new to SQL, since every DBA, as i am, know that this is a trick works. Check this article: http://www.databasejournal.com/featu...le.php/1460151 i didin't read the full article, but it is just the idea... i am not advising to do this, but probably everything will be ok. I have done it a few times in test systems.

The transaction log is a part of the database, but it is not the database...all changes that are made are record on the transaction log.

Originally Posted by RBBOT

You need to shrink the log which you can do by right clicking on the database name in management studio and choose to shrink data files.

Yes, but as i told before, in this situation, first step is to truncate the log, doing a shrink is the second step

Just to clarify, don't delete the log without the truncate, since this is what is going to commit the changes to the database, if you do that, most probably everything will work.

[JustinK101]

Here are the steps I did to fix this problem;

1.) DBCC shrinkfile(dbName_log, 1000 )
2.) Shrink the entire database
3.) Change from FULL recovery to SIMPLE
4.) Did another shrink
5.) Backed up both log and data files
6.) Changed from SIMPLE to FULL
7.) Did another shrink
8.) Backed up both log and data files
9.) Set the max size of the log file to 1GB and autogrowth to 15% to prevent this from happening again.

The transaction log is now like 1.5 MEGS. Yippie.