About DB log file too big? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

About DB log file too big?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-07-26 : 22:51:44

Good day!

I just want to ask if these size is stable or unstable?

My Database size is 101MB and its log file is 2.74GB?

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-27 : 02:56:06

do you've a log backup? itaking log backup will ensure your commited transactions get truncated and log file will not grow too much. Also while doing huge transactions, better to split it up to small batches to avoid log space filling up.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-07-27 : 04:59:02

quote:
Originally posted by visakh16

do you've a log backup? itaking log backup will ensure your commited transactions get truncated and log file will not grow too much. Also while doing huge transactions, better to split it up to small batches to avoid log space filling up.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you visakh16!

Were doing backup every.But its a manual backup procedure..stopping sql server service then copy the database and its corresponding log file from the sqlserver data folder..lately we've noticed that log file is getting more bigger..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-27 : 05:03:15

quote:
Originally posted by adbasanta

quote:
Originally posted by visakh16

do you've a log backup? itaking log backup will ensure your commited transactions get truncated and log file will not grow too much. Also while doing huge transactions, better to split it up to small batches to avoid log space filling up.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you visakh16!

Were doing backup every.But its a manual backup procedure..stopping sql server service then copy the database and its corresponding log file from the sqlserver data folder..lately we've noticed that log file is getting more bigger..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

that may be due to long running transaction and due to lack of proper backup mechanism. Based on amount of transactions you've to schedule log backups periodically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-07-27 : 05:45:35

quote:
Originally posted by visakh16

quote:
Originally posted by adbasanta

quote:
Originally posted by visakh16

do you've a log backup? itaking log backup will ensure your commited transactions get truncated and log file will not grow too much. Also while doing huge transactions, better to split it up to small batches to avoid log space filling up.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you visakh16!

Were doing backup every.But its a manual backup procedure..stopping sql server service then copy the database and its corresponding log file from the sqlserver data folder..lately we've noticed that log file is getting more bigger..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

that may be due to long running transaction and due to lack of proper backup mechanism. Based on amount of transactions you've to schedule log backups periodically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you visakh16!

But what do you mean by long running transaction? Is it related to query issue? And also is it possible to create a query that backs up the databse and log file every end of the day without stopping the sql server service?

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-27 : 08:44:17

nope...it may be query handling large amount of data in transaction.

yep...its possible. create a sql agent job which backups the log and schedule it to run daily at a convenient time at night.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-27 : 10:22:34

quote:
Originally posted by adbasanta
thank you visakh16!

Were doing backup every.But its a manual backup procedure..stopping sql server service then copy the database and its corresponding log file from the sqlserver data folder..lately we've noticed that log file is getting more bigger..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta, this is NOT what you should be doing. For at least two reasons. First, even if you have these backups, in the event of a disaster, these backups may or may not be useful. Second, this will do nothing to prevent your log file from growing.

Do the following in order:

1. In SSMS Object Explorer, right-click on the database name, Tasks-> Backup. This shows you a dialog that allows you to take a backup using the Microsoft-recommended way of doing things. Choose backup type = Full. You can choose where to save the backup file. Or accept the default destination. Once you click OK, it will backup the database. Save this backup file in a safe place.

2. Ask yourself (or your business clients) how much of a data loss they can tolerate. What I mean is, if the server/database were to crash and burn, and if you were able to recover the database as it existed only last night at 8:00 PM, would that be acceptable. If not, what is acceptable.

Let us assume they said that recovering as it existed last night is sufficient. Then do the following. If they say that is not sufficient, then post back what the requirements are and we can figure out a plan that will let you recover to meet the needs.

3. Assuming 1 day data loss is acceptable, do these:
a) right click on the database name in SSMS, properties, options tab and change the recovery model to Simple. Then click OK.
b) open an SSMS query window and run the following

USE YourDatabaseName
GO
CHECKPOINT
GO

c) Rightclick on the database name, Tasks->Shrink->Files, Change the FileType to Log. Take a look at the available free space in that dialog. If it says 90%, that is approximately how much the log file will shrink by (for the most part). Click OK.

Now your log file should be small and you should be good to go.

4) Instead of doing the backups manually, set up an automated procedure to backup the database each night. You can do this by right clicking on Maintenance plans under Server->Management in SSMS object explorer. The wizard will walk you through it. You have to have SQL Server Agent running for this automatic backup feature to work.

Important: What I said above assumes your SLA is one day data loss. If that is not the requirement, post back.

Also, take the full backup that you created, go to a development server and restore that database. So you will know exactly how to do it. The time to figure out how to restore a database is not after a real disaster when your customers are screaming at you to bring the database back online.

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-07-31 : 07:08:36

than you visakh16, James K for the idea..!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources