Nigel_Taylor
asked on
SQL Server Large Log Files
Hi Guys,
Basically I have some very large log files for my SQL Server Databases. We don’t have a DBA and I am in need of assistance.
I have two SQL Servers running Windows 2008 R2 64bit with SQL Server 2008 running Databases with mirroring enabled and have a witness server too.
I have 9 databases setup on the server some more heavily used than others. Our Main database is as follows...
MDF File = 478Mb (Autogrow by 10% Unrestricted)
LDF File = 233GB (Autogrow by 10% Unrestricted)
Last Database Backup 03:34
Last Log File Backup12:30
Recovery Model Full
Mirroring Status "Fully Synchronized"
In SQL Server Agent Manager there is a job scheduled that runs a maintenance plan at 19:00 daily which
Takes Full Backup of the DB to the local disk for all user databases.
Deletes all backup files older than 2 days with .bak or .trn file extension.
There is another job set to take Transaction log backups hourly every day.
In the backups folder there are transaction log backups that are between 200Mb and 700Mb every hour.
Please can you advise what I can do as I don’t believe the log file should be this disproportionate to the Database file?
Running “DBCC OPENTRAN” I get
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (219127:93:1)
Running “DBCC SQLPERF(Logspace)” I Get
Database Name Log Size (MB) Log Space Used (%) Status
Main_Multibrand 239051.6 96.17966 0
Running “select name, log_reuse_wait_desc from sys.databases” I get
name log_reuse_wait_desc
GreenThings NOTHING
Import LOG_BACKUP
Main_Multibrand REPLICATION
Furniture LOG_BACKUP
Assoc LOG_BACKUP
Future LOG_BACKUP
Food LOG_BACKUP
Main_Multibrand_S LOG_BACKUP
discounts LOG_BACKUP
Survey LOG_BACKUP
Home LOG_BACKUP
Care LOG_BACKUP
If you need any further information then please shout.
Regards,
Nigel
Basically I have some very large log files for my SQL Server Databases. We don’t have a DBA and I am in need of assistance.
I have two SQL Servers running Windows 2008 R2 64bit with SQL Server 2008 running Databases with mirroring enabled and have a witness server too.
I have 9 databases setup on the server some more heavily used than others. Our Main database is as follows...
MDF File = 478Mb (Autogrow by 10% Unrestricted)
LDF File = 233GB (Autogrow by 10% Unrestricted)
Last Database Backup 03:34
Last Log File Backup12:30
Recovery Model Full
Mirroring Status "Fully Synchronized"
In SQL Server Agent Manager there is a job scheduled that runs a maintenance plan at 19:00 daily which
Takes Full Backup of the DB to the local disk for all user databases.
Deletes all backup files older than 2 days with .bak or .trn file extension.
There is another job set to take Transaction log backups hourly every day.
In the backups folder there are transaction log backups that are between 200Mb and 700Mb every hour.
Please can you advise what I can do as I don’t believe the log file should be this disproportionate to the Database file?
Running “DBCC OPENTRAN” I get
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (219127:93:1)
Running “DBCC SQLPERF(Logspace)” I Get
Database Name Log Size (MB) Log Space Used (%) Status
Main_Multibrand 239051.6 96.17966 0
Running “select name, log_reuse_wait_desc from sys.databases” I get
name log_reuse_wait_desc
GreenThings NOTHING
Import LOG_BACKUP
Main_Multibrand REPLICATION
Furniture LOG_BACKUP
Assoc LOG_BACKUP
Future LOG_BACKUP
Food LOG_BACKUP
Main_Multibrand_S LOG_BACKUP
discounts LOG_BACKUP
Survey LOG_BACKUP
Home LOG_BACKUP
Care LOG_BACKUP
If you need any further information then please shout.
Regards,
Nigel
If it's Main_Multibrand, the log is not being resused because it's preserving some log recores for replication.
If you still need the replication, you will have to either allow the replication to catch up itself or just turn off replication, shrink the log, turn repl back on, and re-sync the replicated copy from the start with a new db backup.
DON'T use the existing db backup to re-sync replication -- the existing backup will restore the log file to its current size.
Also, change the log file growth to a fixed amount, say 100M, rather than 10%. That's always best, but even more so when the log is so huge, because you don't want to pause the db while SQL formats 25G of log space!
If you still need the replication, you will have to either allow the replication to catch up itself or just turn off replication, shrink the log, turn repl back on, and re-sync the replicated copy from the start with a new db backup.
DON'T use the existing db backup to re-sync replication -- the existing backup will restore the log file to its current size.
Also, change the log file growth to a fixed amount, say 100M, rather than 10%. That's always best, but even more so when the log is so huge, because you don't want to pause the db while SQL formats 25G of log space!
ASKER
Hi Guys,
Thanks for the quick responses.
Things are starting to look better. I ahve gone through using the shrink command on some of the less critical databases and am noticing a big difference on some of the DB's.
For the DB Main_Multibrand I am happy to break the replication on it as I am running out of disk space on our backup server and need to reduce this file size. What would you deem the safest way to stop the replication?
Regards,
Nigel
Thanks for the quick responses.
Things are starting to look better. I ahve gone through using the shrink command on some of the less critical databases and am noticing a big difference on some of the DB's.
For the DB Main_Multibrand I am happy to break the replication on it as I am running out of disk space on our backup server and need to reduce this file size. What would you deem the safest way to stop the replication?
Regards,
Nigel
ASKER
Ok I have dropped the replication on the DB Main_Multibrand and the files are as follows
Before
MDF = 500Mb
LDF = 233GB
After
MDF = 424Mb
LDF = 226GB
There are no Open Transactions in the Database and the log_reuse_wait_desc = LOG_BACKUP. I will wait for the next full backup and see what that does and then update this again.
Regards,
Nigel
Before
MDF = 500Mb
LDF = 233GB
After
MDF = 424Mb
LDF = 226GB
There are no Open Transactions in the Database and the log_reuse_wait_desc = LOG_BACKUP. I will wait for the next full backup and see what that does and then update this again.
Regards,
Nigel
Once you have replication off, you can put the db in SIMPLE mode temporarily.
Then you should be able to shrink the *log* -- DON'T shrink the data files, *just* the *log* file.
USE <database_name>
DBCC SHRINKFILE ( logical_log_file_name, 100 )
For performance, you also don't want to have too many VLFs. So run this command:
DBCC LOGINFO
If that returns more than, say, 60, shrink the log as much as possible and then re-alloc the log space all at once:
DBCC SHRINKFILE ( logical_log_file_name, 1 )
ALTER DATABASE <database_name> MODIFY FILE ( NAME = logical_log_file_name, SIZE = 100MB )
Then you should be able to shrink the *log* -- DON'T shrink the data files, *just* the *log* file.
USE <database_name>
DBCC SHRINKFILE ( logical_log_file_name, 100 )
For performance, you also don't want to have too many VLFs. So run this command:
DBCC LOGINFO
If that returns more than, say, 60, shrink the log as much as possible and then re-alloc the log space all at once:
DBCC SHRINKFILE ( logical_log_file_name, 1 )
ALTER DATABASE <database_name> MODIFY FILE ( NAME = logical_log_file_name, SIZE = 100MB )
Then, you can put the db back in FULL mode, take a full db backup, and you should be good from then on.
ASKER
Thanks for the help Scott.
For the Main multibrand database the MDF file is now 466MB and the LDF is 600MB and queries over the DB are noticably faster.
The full back for the DB is now 420MB. Is there a way of sense checking that the transaction logs backed up hourly are correct to still be between 300Mb and 800Mb?
Regards,
Nigel
For the Main multibrand database the MDF file is now 466MB and the LDF is 600MB and queries over the DB are noticably faster.
The full back for the DB is now 420MB. Is there a way of sense checking that the transaction logs backed up hourly are correct to still be between 300Mb and 800Mb?
Regards,
Nigel
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When prompted, just click OK.
This should shrink both the database and the transaction logs in one foul swoop. If you find that this has helped you, then add a shrink database action to your full backup (not transaction log backup) maintenance task.