al4629740
asked on
Transaction Log file is HUGE
I noticed that the data section of my server is getting more full. When I looked at the data file for the SQL server, it was roughly 1.1GB However, I noticed the Transaction Log File is 107 GB!
What is the transaction log file? Why does it get so huge? Do I need the information and can I shrink it?
I am afraid to do anything to it as I do not want to cause harm to the server...
What is the transaction log file? Why does it get so huge? Do I need the information and can I shrink it?
I am afraid to do anything to it as I do not want to cause harm to the server...
...and make sure to take backups in the future....
If you do not have a backup solution, use mainenance plans to backup the databases to avoid th eissue in the future.
If you do not have a backup solution, use mainenance plans to backup the databases to avoid th eissue in the future.
ASKER
I do have a maintenance plan that backs the database up to another folder. What is the purpose of the log file?
SQL requires a log file to insure database consistency.
You can also use the log file to recover your database to a point in time if you want to. In order to do that, you must put the db in FULL (or BULK-LOGGED) recovery mode and you must backup the log files.
If you don't need or want the forward recovery capability, you can leave the db in SIMPLE recovery mode. But, if the db is lost or damaged, you can only recover to the point of a full or differential backup.
An example is probably clearer.
(1) Db "Db1" is in "FULL" mode; full backup is at 8PM, log backups every half hour.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM, and apply the logs from 9PM through 3:30AM to recover the db as it looked at 3:30AM. All data added/changed past 3:30AM is lost.
(2) Db "Db1" is in "SIMPLE" mode; full backup is at 8PM, no log backups.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM. All data added/changed past 8PM is lost.
You can also use the log file to recover your database to a point in time if you want to. In order to do that, you must put the db in FULL (or BULK-LOGGED) recovery mode and you must backup the log files.
If you don't need or want the forward recovery capability, you can leave the db in SIMPLE recovery mode. But, if the db is lost or damaged, you can only recover to the point of a full or differential backup.
An example is probably clearer.
(1) Db "Db1" is in "FULL" mode; full backup is at 8PM, log backups every half hour.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM, and apply the logs from 9PM through 3:30AM to recover the db as it looked at 3:30AM. All data added/changed past 3:30AM is lost.
(2) Db "Db1" is in "SIMPLE" mode; full backup is at 8PM, no log backups.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM. All data added/changed past 8PM is lost.
ASKER
So basically you think my db is in FULL mode. How can I tell?
SELECT
name, recovery_model_desc, log_reuse_wait_desc,
is_cdc_enabled, snapshot_isolation_state_d esc, is_read_committed_snapshot _on
FROM sys.databases
WHERE name = '<your_db_name>'
name, recovery_model_desc, log_reuse_wait_desc,
is_cdc_enabled, snapshot_isolation_state_d
FROM sys.databases
WHERE name = '<your_db_name>'
ASKER
name recovery_model_desc log_reuse_wait_desc is_cdc_enabled snapshot_isolation_state_desc is_read_committed_snapshot_on
CAPRegistration FULL LOG_BACKUP 0 OFF 0
ASKER
Is that a default setting or did I set that up?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can indeed shrink it.
Set the db to simple and shrink the log file.
For example:
USE <db_name>
--find and copy/save the logical name of log file using command below;
--the first column is the logical file name
EXEC sp_helpfile
ALTER DATABASE <db_name>
SET RECOVERY SIMPLE
DBCC SHRINKFILE ( 2 )
ALTER DATABASE <db_name>
MODIFY FILE ( NAME = <logical_file_name_for_log