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.
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.
ASKER
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.
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.
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.
I'd start by double checking the configuration of the log file and what settings have been applied.
ASKER
it is growing and shrinking.
On my backup exec I have set for SQL : Backup and truncate Transaction log
On my backup exec I have set for SQL : Backup and truncate Transaction log
You would most likely have autogrow enabled on your log. You can view more about this and autoshrink here:
http://support.microsoft.c
You can view the setting of your database by issuing the following command:
http://support.microsoft.c
You can view the setting of your database by issuing the following command:
sp_helpdb [ [ @dbname= ] 'yourdatabasename' ]ASKER
the command is not working
Where to put my db name in ?
Where to put my db name in ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok the DB is 22GB.
The log is 84MB with maxsize=unlimited and grow 1024KB.
But I cannot see the shrink parameters
The log is 84MB with maxsize=unlimited and grow 1024KB.
But I cannot see the shrink parameters
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
??
And size on start is 80MB.
Without impacts for the user, can I change both ?
Autoshrink to true
start size to 1GB
??
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 explains why it is shrinking a truncate deletes everything from the transaction log
You can.
ASKER
thanks
ASKER
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.
But this one stays the same size , no shrinking.
In SQL manager autoshrink is off.
>> 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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok the size increased now to 19GB.
This is nearly the size of the DB itself.
This is nearly the size of the DB itself.
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>'
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>'
ASKER
Recovery model is FULL.
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).
ASKER
of course the logs are backed up all two hours.
Thanks.
Thanks.
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 ...