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.
| Author |
Topic |
|
sridharsridhar
Starting Member
21 Posts |
 Posted - 2013-07-23 : 05:09:42
|
| Hi AllCan 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
52326 Posts |
Posted - 2013-07-23 : 05:17:06
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
sridharsridhar
Starting Member
21 Posts |
Posted - 2013-07-23 : 05:29:47
|
| 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 .thankssridhar |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 08:31:53
|
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 .thankssridhar
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-24 : 02:31:00
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Decreasing the size of log file