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)
 How to shrink Log file

Author  Topic 

GaryNull
Starting Member

14 Posts

Posted - 2013-04-02 : 16:25:22
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-02 : 16:42:47
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-02 : 16:51:06
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

14 Posts

Posted - 2013-04-02 : 17:33:40
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

1974 Posts

Posted - 2013-04-02 : 17:48:37
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

14 Posts

Posted - 2013-04-03 : 10:09:41
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

5072 Posts

Posted - 2013-04-03 : 10:27:45
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-03 : 14:49:30
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

5072 Posts

Posted - 2013-04-03 : 15:39:57
If the database isn't in simple...
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-03 : 15:45:36
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

1974 Posts

Posted - 2013-04-03 : 15:50:11
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

5072 Posts

Posted - 2013-04-03 : 15:50:34
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

819 Posts

Posted - 2013-04-04 : 13:13:46
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
   

- Advertisement -