Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

log size of DB always changed

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
Eprs_Admin
Asked:
Eprs_Admin
  • 10
  • 4
  • 3
  • +3
3 Solutions
 
Vikas GargBusiness Intelligence DeveloperCommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Eprs_AdminSystem ArchitectAuthor Commented:
it is growing and shrinking.
On my backup exec I have set for SQL : Backup and truncate Transaction log
0
 
Randy PooleCommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
the command is not working
Where to put my db name in ?
0
 
Randy PooleCommented:
You would do it as:
sp_helpdb @dbname='databasename'

Open in new window

and change databasename to the name of your database
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
ok the DB is 22GB.
The log is 84MB with maxsize=unlimited and grow 1024KB.
But I cannot see the shrink parameters
0
 
Randy PooleCommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
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
 
David Johnson, CD, MVPOwnerCommented:
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
 
Randy PooleCommented:
You can.
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
thanks
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
David Johnson, CD, MVPOwnerCommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
ok the size increased now to 19GB.
This is nearly the size of the DB itself.
0
 
Scott PletcherSenior DBACommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
Recovery model is FULL.
0
 
Scott PletcherSenior DBACommented:
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
 
Eprs_AdminSystem ArchitectAuthor Commented:
of course the logs are backed up all two hours.
Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 10
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now