SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 About DB log file too big?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 07/26/2013 :  22:51:44  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 07/27/2013 :  02:56:06  Show Profile  Reply with Quote
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
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 07/27/2013 :  04:59:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/27/2013 :  05:03:15  Show Profile  Reply with Quote
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
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 07/27/2013 :  05:45:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/27/2013 :  08:44:17  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/27/2013 :  10:22:34  Show Profile  Reply with Quote
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.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 07/31/2013 :  07:08:36  Show Profile  Reply with Quote
than you visakh16, James K for the idea..!



-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000