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)
 How to shrink Log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryNull
Starting Member

USA
14 Posts

Posted - 04/02/2013 :  16:25:22  Show Profile  Reply with Quote
I tried to shrink my Log file using the Admin :

Shrink action
(checked this) : Reorganize pages before releasing unused space

shrink file to : 10 MB

I hit OK but it didn't shrink it.

This is how big it is :

Data file : 150.00 MB

Log file : 1441.31 MB


How can I shrink the Log file?
I don't really need it for anything because I don't
use transactions or roll backs.

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/02/2013 :  16:42:47  Show Profile  Reply with Quote
It may be that you don't have any inactive vlf's. That can happen for a number of reasons, but sounds like you don't really care much about that.

You can change the recovery mode to simple and then shrink the log file. Changing the recovery model is in properties -> options menu that you can get by right-clicking on the database name. Doing so will result in LSN's being reset. Take a full backup of the database before you do this.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/02/2013 :  16:51:06  Show Profile  Reply with Quote
quote:
Originally posted by GaryNull

I don't really need it for anything because I don't
use transactions or roll backs.

You do realize that SQL Server does through, right? That's part and parcel for the ACID properties on a (mostly) relational database management system.
Go to Top of Page

GaryNull
Starting Member

USA
14 Posts

Posted - 04/02/2013 :  17:33:40  Show Profile  Reply with Quote
thanks guys,

Is that log file too big and do I really need to shrink it?
Should I just leave it alone?
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/02/2013 :  17:48:37  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by GaryNull

thanks guys,

Is that log file too big and do I really need to shrink it?
Should I just leave it alone?




Most likely it is too big. Are you doing TLog backups? If you aren't you either need to start doing them, or (If you don't require Point in time recovery) change your recovery model to simple.

-Chad
Go to Top of Page

GaryNull
Starting Member

USA
14 Posts

Posted - 04/03/2013 :  10:09:41  Show Profile  Reply with Quote
The real problem is that I'm running out of disk space on my hosting account.
I also have 3 other databases on there with the same Log problem.
I was hoping to free up some space by shrinking the logs.
Not really sure what to do at this point.



Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  10:27:45  Show Profile  Visit russell's Homepage  Reply with Quote
That is a tiny log file. You need more disk space.

How much space do they allow you?

What else is hogging up space besides your database files?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 04/03/2013 :  14:49:30  Show Profile  Visit jackv's Homepage  Reply with Quote
Another option is to commit regular log backups

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  15:39:57  Show Profile  Visit russell's Homepage  Reply with Quote
If the database isn't in simple...
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 04/03/2013 :  15:45:36  Show Profile  Reply with Quote
You might try
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/


djj
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/03/2013 :  15:50:11  Show Profile  Visit chadmat's Homepage  Reply with Quote
You can also look at Log_reuse_wait_desc in sys.databases to see why it won't shrink.

-Chad
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  15:50:34  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by GaryNull

thanks guys,

Is that log file too big and do I really need to shrink it?
Should I just leave it alone?




It is not too big.
Yes, you should leave it alone.

But do make sure that the database is in simple recovery model.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 04/04/2013 :  13:13:46  Show Profile  Reply with Quote
quote:
Originally posted by GaryNull

The real problem is that I'm running out of disk space on my hosting account.
I also have 3 other databases on there with the same Log problem.
I was hoping to free up some space by shrinking the logs.
Not really sure what to do at this point.



First, determine the recovery model of each database that has this problem.

If the recovery model is full, then you need to determine whether or not you need the ability to recover to a point in time. If you are okay with being able to recover to the last backup, then you can switch the recovery model to simple. If you are not okay with losing that much data - then you need to make sure you are running transaction log backups frequently. How frequently will be determined by how much data loss you can accept - for example, if losing an hours worth of data is okay then run your transaction log backups at least every hour (or every 30 minutes to be safer).

Once you have done the above, then you can monitor the usage and shrink the log files down to normal operating size to handle the amount of transactions occurring during the hour (or half-hour) interval between log backups.

I would recommend that you backup your databases daily, and backup the transaction log at least every hour if not more frequently.
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.11 seconds. Powered By: Snitz Forums 2000