October 16, 2007 at 11:23 pm
Hi,
My company uses SQL Server 2005. We have a database which is not that large (aroung 5 GB) but the transaction log is now around 17GB and increasing daily. The Autogrow setting is enabled.
How to to auto shrink. Please Help.
Regards
Jagadeesh
October 17, 2007 at 1:51 am
Hi,
What recovery mode is your database in?
select databasepropertyex('databasename', 'Recovery')
If you it is in simple recovery mode, check if you have any transactions that are still running.
If your database is not in simple recovery mode, then how often are you backing up your transaction log?
If you are not backing it up, the transaction log will continue growing, as all the log records will be kept.
If you do backup the transaction log, parts of the log can be reused, and the size will likely stabilize.
If you want to reduce the size to the size that will be likely stable, you can use DBCC SHRINKFILE after backing up the log.
Kind regards,
Andras
October 17, 2007 at 4:15 am
What Andras says is spot on. To simplify a little - if you are already backing up the database, and don't know or care about the logs, change the Recovery Model (found under Database Properties, Options) to Simple, then shrink the log file using the command Andras explained. You will lose all your transaction logs, but it sounds like you are not using them anyway.
October 18, 2007 at 4:00 am
Transaction log growing?
No backups?
What about when you SQL Server fails, all those lovely transactions will disappear....what will you restore? Any database backups? Any development server to restore the metadata and designs from (DDL/DML)?
No backups on a production system is not an option.
If this is a test system then go simple as the transactions will be tests.....for any other system (Dev and esp. Live) you need to backup or face loosing everything.
hth's
October 18, 2007 at 4:24 am
Hi
Andras is right here.
I think the best long term option would be to take log backups.
"Keep Trying"
October 18, 2007 at 7:51 am
Ditto above and I would not recommend you do too much shrinking of the log file as continued shrinking and growing will slow the system down. Instead, try to find a happy medium where your size stays consistent - the log backups truncate the data, then the log file starts filling up again, the log backup truncates ... Also note if your doing big changes (adding indexes, altering tables ...) you can cause big log growth and its not a bad idea to shrink it back down to the manageable size once these operations are done.
October 19, 2007 at 5:58 am
You also could add a step, after backing up the transaction log :
dump transaction mydatabasename with no_log
dump log mydatabasenameg with no_log
/* no shrink DB */
EXECUTE sp_updatestats
Then shrinking database
October 19, 2007 at 6:03 am
rabartels (10/19/2007)
You also could add a step, after backing up the transaction log :dump transaction mydatabasename with no_log
dump log mydatabasenameg with no_log
/* no shrink DB */
EXECUTE sp_updatestats
Then shrinking database
EXECUTE sp_updatestats is a good idea, but please use the "BACKUP" command instead of "dump" 🙂 dump still works, but it is included only for backward compatibility, and will be removed in future versions of SQL Server.
Regards,
Andras
October 19, 2007 at 6:50 am
Andras Belokosztolszki (10/19/2007)
rabartels (10/19/2007)
You also could add a step, after backing up the transaction log :dump transaction mydatabasename with no_log
dump log mydatabasenameg with no_log
/* no shrink DB */
EXECUTE sp_updatestats
Then shrinking database
EXECUTE sp_updatestats is a good idea, but please use the "BACKUP" command instead of "dump" 🙂 dump still works, but it is included only for backward compatibility, and will be removed in future versions of SQL Server.
Regards,
Andras
Thanks for the info, I will take it into account for SQL 2008. one of our developed applications is monitoring the transaction logs. We already advise a maintaince plan for our databases at customers, in which the database is backed up, transaction logs, on a daily basis etc
Cheers,
Xander
October 19, 2007 at 4:37 pm
hi
just an anxiety, do you take full backups & differential backups.. if you take differential backups, take differential backups first & truncate or shrink log file.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply