Solved

log size of DB always changed

Posted on 2014-07-23
21
234 Views
Last Modified: 2014-07-24
Hi Experts,

I have a DB on a SQL server 2008.
With a script I always check the log size of the DB.
But here the DB has always different log size and therefore the log is sometime 99% full and sometime not.
Which process changed the size of the log ?
I just want the log to be empty, this is done with my backup exec tool.
0
Comment
Question by:Eprs_Admin
  • 10
  • 4
  • 3
  • +3
21 Comments
 
LVL 14

Expert Comment

by:Vikas Garg
Comment Utility
Hi,

Each Insert ,Update and Delete task will increase the log file size since it will be logged.

However you can control the size of the Log file by setting its File Size but it can't be emptied ...
0
 

Author Comment

by:Eprs_Admin
Comment Utility
Yes this I know.
But why the log file size is changed all the time ?
I have another log file which has always the same size. maybe it is 20% filled but the size is always the same.
Some process is changing also the size.
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Is the size increasing, or is it shrinking too? The log file will grow by itself (depending on configuration) when it needs to and can also be set to autoshrink (although that isn't recommended).

I'd start by double checking the configuration of the log file and what settings have been applied.
0
 

Author Comment

by:Eprs_Admin
Comment Utility
it is growing and shrinking.
On my backup exec I have set for SQL : Backup and truncate Transaction log
0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
You would most likely have autogrow enabled on your log.  You can view more about this and autoshrink here:
http://support.microsoft.com/kb/315512
You can view the setting of your database by issuing the following command:
sp_helpdb [ [ @dbname= ] 'yourdatabasename' ]

Open in new window

0
 

Author Comment

by:Eprs_Admin
Comment Utility
the command is not working
Where to put my db name in ?
0
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 334 total points
Comment Utility
You would do it as:
sp_helpdb @dbname='databasename'

Open in new window

and change databasename to the name of your database
0
 

Author Comment

by:Eprs_Admin
Comment Utility
ok the DB is 22GB.
The log is 84MB with maxsize=unlimited and grow 1024KB.
But I cannot see the shrink parameters
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 334 total points
Comment Utility
IN SQL Manager if you right click on your database and select properties, select Files to the left, you can see and set your auto growth, click on the options to the left and you can see and set  auto shrink
0
 

Author Comment

by:Eprs_Admin
Comment Utility
ok, autoshrink is set to true.
And size on start is 80MB.

Without impacts for the user, can I change both ?
Autoshrink to true
start size to 1GB

??
0
Want to promote your upcoming event?

Attending an event? Speaking at a conference? Or exhibiting at a tradeshow? Easily inform your contacts by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
On my backup exec I have set for SQL : Backup and truncate Transaction log

That explains why it is shrinking a truncate deletes everything from the transaction log
0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
You can.
0
 

Author Comment

by:Eprs_Admin
Comment Utility
thanks
0
 

Author Comment

by:Eprs_Admin
Comment Utility
I have the same setting on another DB, Backup and truncate Transaction log.
But this one stays the same size , no shrinking.
In SQL manager autoshrink is off.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> On my backup exec I have set for SQL : Backup and truncate Transaction log

 That explains why it is shrinking a truncate deletes everything from the transaction log
<<

That's not correct.  In SQL Server, log "truncation" simply marks logical files in the log as available for reuse to store new log records.  A truncate does not "delete" anything in the log file, nor does it shrink the log file.
0
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 166 total points
Comment Utility
you should keep your log files at a constant size by turning off the autoshrink .. Why you ask, when the file needs to increase in size it blocks sql server until the resize is complete.
0
 

Author Comment

by:Eprs_Admin
Comment Utility
ok the size increased now to 19GB.
This is nearly the size of the DB itself.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Check the database's recovery model.  If it's "FULL", you'll have to back up the log before the space in it can be re-used.  If you don't want or need to do point-in-time recovery, change the recovery to SIMPLE and SQL will automatically re-use log space as soon as it can.

It's also technically possible that replication or some other process is holding log space.

You can determine both of those by looking at the output from:

SELECT * FROM sys.databases WHERE name = '<your_db_name>'
0
 

Author Comment

by:Eprs_Admin
Comment Utility
Recovery model is FULL.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
As long as you are in FULL mode, then the log must be backed up, or it will continue to grow.  You'd have to change back to SIMPLE to avoid doing a log backup (and a backup on a 19GB log file will take a while).
0
 

Author Comment

by:Eprs_Admin
Comment Utility
of course the logs are backed up all two hours.
Thanks.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now