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
 SQL Server Administration (2008)
 Decreasing the size of log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sridharsridhar
Starting Member

India
21 Posts

Posted - 07/23/2013 :  05:09:42  Show Profile  Reply with Quote
Hi All
Can you tell the process of decreasing the size of log file in SQL 2008 . our log file is upto 10 GB so i want to reduce the size.

sridhar

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  05:17:06  Show Profile  Reply with Quote
Whats the recovery model used?
do you've a log backup chain running? taking log backup will truncate committed transactions from log and avoid transaction log from growing too big.

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

Edited by - visakh16 on 07/23/2013 05:17:33
Go to Top of Page

sridharsridhar
Starting Member

India
21 Posts

Posted - 07/23/2013 :  05:29:47  Show Profile  Reply with Quote
Hi Visakh
while restoring i had used Restore with recovery option . log file is not saved in LDF extension also . when i tried to check the properties its showing just file extension. there is no extension for it . its just showing database name in properties .

thanks

sridhar
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/23/2013 :  08:31:53  Show Profile  Reply with Quote
quote:
Originally posted by sridharsridhar

Hi Visakh
while restoring i had used Restore with recovery option . log file is not saved in LDF extension also . when i tried to check the properties its showing just file extension. there is no extension for it . its just showing database name in properties .

thanks

sridhar

Restoring with recovery is fine. That is the default option. What you want to look up is the "recovery model" used by the database. You can find that in the properties dialog of the database or run this query:
SELECT DATABASEPROPERTYEX('YourDatabaseNameHere', 'RECOVERY')

If that says "FULL" or "BULK LOGGED" then, your log file will monotonically grow UNLESS you take log backups.

When you take log backup, it clears the space within the log file (with some restrictions - only free "virtual log files" will be cleared) and makes it available for reuse. That means the log file will not monotonically grow.

If your log file is currently 10 GB, and you want to make it smaller, AFTER taking the back up shrink the log file. That option is in Tasks -> Shrink menu that you will see if you right-click on the database name in SSMS object explorer.

Editing: Here is documentation on log backup http://msdn.microsoft.com/en-us/library/ms179478.aspx You should schedule regular log backups.

Edited by - James K on 07/23/2013 08:32:43
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  02:31:00  Show Profile  Reply with Quote
One more case where transaction log can grow too big is when you run large volume transactions. In such cases you may be better off splitting up transactions into small batches rather than doing all in one shot so log file dont grow too large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.05 seconds. Powered By: Snitz Forums 2000