Solved

log size of DB always changed

Posted on 2014-07-23
21
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 3
  • +3
21 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213830
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
ID: 40213840
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
ID: 40213957
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:Eprs_Admin
ID: 40214002
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
ID: 40214100
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
ID: 40214138
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
ID: 40214183
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
ID: 40214221
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
ID: 40214233
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
ID: 40214276
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
 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 40214379
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
ID: 40214450
You can.
0
 

Author Comment

by:Eprs_Admin
ID: 40214514
thanks
0
 

Author Comment

by:Eprs_Admin
ID: 40214524
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:Scott Pletcher
ID: 40214648
>> 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 81

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 166 total points
ID: 40215388
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
ID: 40216174
ok the size increased now to 19GB.
This is nearly the size of the DB itself.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40216994
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
ID: 40217014
Recovery model is FULL.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40217088
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
ID: 40217099
of course the logs are backed up all two hours.
Thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A procedure for exporting installed hotfix details of remote computers using powershell
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

752 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