January 28, 2008 at 7:46 am
Hello,
I am relatively new to SQL 2005.
I have recently had this issue and resolved it by doing a full backup and shrink on the log file.
But today it has reared it's ugly head again.
I have a log file that was set up by a consultant, and it is set to 156 GB.
The drive is 160GB, and it filled it.
My questions are
1.) Can I set up another log file on a different drive and redirect transactions there untill I get the other one shrunk back down?
2.) When you run a full backup, doesn't it back up the log file as well, and when that happens you can shrink the log file back down?
3.) Can I change the size of my log file from 156 GB to lets say 5GB and allow for the 10% growth and set a max size of 145 GB?
I am stuck and I don't exactly know where to go from here.
Any help would be appreciated.
Aazzner
January 28, 2008 at 7:51 am
Is the database in full recovery mode? Do you have any transaction log backups running?
Full backups don't truncate the inactive portion of the log. Only a transaction log backup does that. If you're in full recovery mode, you need tran log backups. The frequency is determined by how active the system is.
If you don't need to recover the DB to a point in time (recovery to last full backup is acceptable) then you can set the database into simple recovery mode. In simple the log truncates itself after a checkpoint and you won't need tran log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 7:57 am
Also - just exactly how big is your database? 156GB on a log file seems extreme, unless you're dealing with a database with rather special needs...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 8:12 am
It is our Sharepoint database log.
Our company has moved all of its documentation ect. to our internal website which is a Sharepoint site.
So everytime someone opens a docuement, and clicks save and closes it, you have transactions.
The actual main database is only 32 GB.
I think that the 156 GB is extreme as well.
The consultant that set it up was not an SQL person, rather was a network person.
I was not there at the time, and did not know what was done, as well as the fact that I am not certain what should be a standard.
We are in full recovery mode. We are running a backup of the logs right now.
Hopefully that will fix the full log.
Was it not being emptied then?
According to my cohort, we had never backed up the log file, only the database.
I was thinking that we need to make a weekly backup routine to keep this from happening.
I thought originally that our backup sofware would take care of it, but apparently it does not.
Are we moving in the right direction?
Aazzner
January 28, 2008 at 8:28 am
Looking good.
You may want your tran log backups more often. Maybe once every 2-12 hours, depending how many transactions there are. How often are your full/diff backups.
Once the log has been backed up, you can shrink it. The backup doesn't shrink the file, just removes the inactive log records within. Maybe try shrinking the log to 5GB and see how much it grows between log backups. If it's too much, shorten the interval between the log backups.
You're aiming for having the log file a steady size, not needing to grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 8:31 am
Doing database backups do not take care of the logs. When you do a "full" backup, SQL Server backups the contents of the database and enough of the tran logs to keep the database consistent. Implement Hourly to daily (depending on the amount of activity) log backups. After the log backup is complete, it removes the transaction from the file. This doe NOT shrink the file at the OS level, just frees up space internally. If you want to free up OS space, do a "shrinkfile" on the individual log files.
Joseph
January 28, 2008 at 8:36 am
GilaMonster (1/28/2008)
You're aiming for having the log file a steady size, not needing to grow.
Agreed - and when you find this stable place - don't shrink the log file anymore. Like Gila pointed out - you want that number stable, so - find the magic number, and "leave it there". Shrink it once or just a few times at most, while you're looking for that balance, and then just don't do it anymore. With the log backups in place - the size should be consistent.
Continuously shrinking and growing tend to wreck your performance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 10:15 am
Ok, here we go.
The backup of the log file is complete. The backup file says it is 73 GB.
I ran the shrink on the log file, and ie was completed in approximately 3 seconds.
The log file however is still said 156 GB.
I then did another backup and the log file was down to 2GB.
I then did aother shrink on the log file again and set it to 5GB.
But when I looked at the actual .ldf file it was 76GB.
What now?
Aazzner
January 28, 2008 at 10:39 am
That seems to point to the fact that you've got a transaction still not committed somewhere. The backup, etc.... will only backup and truncate committed transactions up to the first uncommitted transaction it runs into. This might also happen if you have replication going on, and the 76GB is the balance of everything that has yet to replicate.
Try running DBCC OPENTRAN to see if there are open transactions out there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 10:50 am
The backup log does not correlate with physical file size on the OS. It is relevant to the "free space" found in the log. Right click on the database and do a shrink database. Once in there, select shrink file, then select the log file. It will tell you the physical space and the amount of free space. Just click OK. The physical file will decrease.
January 28, 2008 at 11:38 am
Ok, now I am really confused.
I highlighted the database name on the right.
Clicked New Query
Typed DBCC OPENTRAN in the window
Clicked Execute
It told me no open transactions.
That floors me.
I would expect at least 1 out there somewhere.
And if it is correct, then why in the heck did my logfile shrink to only 76GB when I told it to go to 5GB?
Granted it went from 156GB to 76GB which is half the size, but still.
Personally,the way it makes sense to me is this.
The log file is the size of an empty candy jar.
Every transaction is a jelly bean.
Backing up the log file dumps out all the old jelly beans.
If they are all old, then the jar is empty.
If I tell the jar to be 5GB and it stops at 76GB and all the jelly beans are old, why is it still 76GB and not 5GB?
Confused,
Aazzner
January 28, 2008 at 11:52 am
When you shrunk the log file, did you return the free space to the operating system? If not the file will still maintain the large size.
January 28, 2008 at 12:09 pm
On a production database, I would perform this off hours and when you have admin control.
You can "backup log with truncate_only", this will get rid of all uncommitted transactions.
DO A FULL BACKUP!
IMPORTANT: This BREAKS the backup trail. You can NOT go back to a previous backup and apply transaction log backups.
Do a properties on the database. Take a look in the Database and Log files to see what is data and what is freespace. You can do a shrink file again, just let it shrink down down to what it can.
I would also recommend you change the behavior of the log and data files. Set a max file size. Depending on the system, I have the set from 2048 to over 43gig each. Then increase the size incrementally, 10meg for small systems, 100meg for large. Better way to gage growth.
Post : OS file size =
DB size =
Free space =
Joseph
January 28, 2008 at 12:53 pm
a log file is made up of virtual logs, and will only shrink down to the last virtual log with active transactions, yours must be at the 73GB mark.
run the following command:
dbcc loginfo(yourdbname)
which will display your log file with one row per virtual log. Those with a value of 2 in the status column contain active transactions, and the log will not shrink past the last one with this value. You will have to wait for the transactions to wrap round or force a wrap around by filling the tran log yourself.
BOL contains info on this, a search on 'virtual log files' should find it.
HTH
george
---------------------------------------------------------------------
January 28, 2008 at 1:54 pm
First let me say I am running the shrink function from the GUI interface in SSMS.
Right click database - tasks - shrink - files
then do the log files.
Under the options for backing up you can do a simple shrink or shrink and reorganize page sizes and set file size.
The last time I shrunk the log file I used the reorganize option and set it to 5000 MB (the standard measure on my system).
That is why I was confused about the difference between 5GB and 76GB.
When we started getting things done to fix this - the first thing we did was a full backup.
Size information
OS - C:\ 117 GB (SQL program files)
E:\ 136 GB (Database Files)
F:\ 156 GB (167,721,680,896 bytes) (Log placement)
G:\ 136 GB (Backup and other Database files)
Database file size 33,357,824 KB
Log file size 76,246,144 KB
According to the numbers that come up when you open the shrink option for the log file it states:
Current Allocated Space: 74459.13MB
Available Free Space: 73255.52MB
I have run it wiwth the shrink action as
Release unused space
Reorganize pages before releasing unused space - Shrink file to 5000 MB.
All my file sizes stayed the same.
hmmmm.
Aazzner
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply