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 MBLog file : 1441.31 MBHow 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. |
|
|
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. |
|
|
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? |
|
|
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 |
|
|
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. |
|
|
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? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-04-03 : 14:49:30
|
Another option is to commit regular log backupsJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-03 : 15:39:57
|
If the database isn't in simple... |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
|
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 |
|
|
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. |
|
|
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. |
|
|
|