Solved

log size of DB always changed

Posted on 2014-07-23
21
249 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 82

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 82

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

636 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