garsmi
asked on
MS SQL 2008 shrinking database and log file disk space usage
Hey folks,
I am trying to set up a proper regular routine for shrinking database files. The main reason I want the shrinking to be put in place is that my log file usage is ridiculously low (e.g. 0.5%), means the log file uses gigabytes on the disk whereas stores only megabytes of the log data only.
This is my configuration:
SQL Server 2008 64-bit
Database compatibility level 80 (SQL Server 2000)
Recovery mode = Full
To be able to effectively use SHRINKDATABASE or SHRINKFILE I need to switch the database to simple recovery mode. After performing dbcc SHRINKDATABASE('mydbname',
Any ideas of how to achieve an optimal log file disk space usage?
Thanks in advance for sharing your thoughts on this!
I am trying to set up a proper regular routine for shrinking database files. The main reason I want the shrinking to be put in place is that my log file usage is ridiculously low (e.g. 0.5%), means the log file uses gigabytes on the disk whereas stores only megabytes of the log data only.
This is my configuration:
SQL Server 2008 64-bit
Database compatibility level 80 (SQL Server 2000)
Recovery mode = Full
To be able to effectively use SHRINKDATABASE or SHRINKFILE I need to switch the database to simple recovery mode. After performing dbcc SHRINKDATABASE('mydbname',
Any ideas of how to achieve an optimal log file disk space usage?
Thanks in advance for sharing your thoughts on this!
This article can hopefully shed some light also http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space
Hi,
When switching back to full recovery model, the very next thing you are supposed to do is take a full database backup.
What is the size of the database? Given the standard answer for most DBA questions is it depends, and the depends for your log file of 4GB is what is the database size?
I suggest that the repeated shrinking and growth is bad for performance - it fragments the physical log files, it creates masses of virtual log files (vlfs), every time the file grows it needs to be zeroed or initialised. (Instant file initialisation only applies to data files, not log files)
If you say that your database size is 100GB then a 4 GB log file is really small. On the other hand, if the data file is 1GB then I agree that 4GB is way too much too big.
If the log file is immediately growing back to 4GB then you should check out what the growth increment is set to. And if 4GB is the sweet spot it sits at then let it be. If as just above 4GB is out of proportion to the database size, then you need to investigate why that is happening - what features are you using that could contribute to that size? Do you have a lot of varchar( max ) or other large objects being updated?
Are you taking transaction log backups as well? How often? How big are they?
Regards
David
When switching back to full recovery model, the very next thing you are supposed to do is take a full database backup.
What is the size of the database? Given the standard answer for most DBA questions is it depends, and the depends for your log file of 4GB is what is the database size?
I suggest that the repeated shrinking and growth is bad for performance - it fragments the physical log files, it creates masses of virtual log files (vlfs), every time the file grows it needs to be zeroed or initialised. (Instant file initialisation only applies to data files, not log files)
If you say that your database size is 100GB then a 4 GB log file is really small. On the other hand, if the data file is 1GB then I agree that 4GB is way too much too big.
If the log file is immediately growing back to 4GB then you should check out what the growth increment is set to. And if 4GB is the sweet spot it sits at then let it be. If as just above 4GB is out of proportion to the database size, then you need to investigate why that is happening - what features are you using that could contribute to that size? Do you have a lot of varchar( max ) or other large objects being updated?
Are you taking transaction log backups as well? How often? How big are they?
Regards
David
ASKER
Thank you for the answers.
I'll try to sum up my comments:
# CL 80 has to be kept, using a higher CL is a "no go", don't think it this much influences the shrinking and / or log file size
# it's a transactional (back-end) database, DML statements being the most frequently occuring types of transactions, avg rate of txs = 10 per minute.
# the total size of the db is 14GB, thus, the 4GB log seems rather big to me
# am aware of the fragmentation when running the shrink too often, I want to run it once a month
# the log file is set to increment by 10%
# no large objects (data types) used
# transaction log backups are done hourly, full db backup daily
# the log files do not ge truncated after the backup is done
# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.
Two questions:
# When do I need to do the post-shrink full db backup? Before or after the db is set back to full recovery mode? Just wondering if this anyhow matters..
# Is there a way to do an effective db shrink w/o putting the db to simple recovery mode beforehand?
I'll try to sum up my comments:
# CL 80 has to be kept, using a higher CL is a "no go", don't think it this much influences the shrinking and / or log file size
# it's a transactional (back-end) database, DML statements being the most frequently occuring types of transactions, avg rate of txs = 10 per minute.
# the total size of the db is 14GB, thus, the 4GB log seems rather big to me
# am aware of the fragmentation when running the shrink too often, I want to run it once a month
# the log file is set to increment by 10%
# no large objects (data types) used
# transaction log backups are done hourly, full db backup daily
# the log files do not ge truncated after the backup is done
# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.
Two questions:
# When do I need to do the post-shrink full db backup? Before or after the db is set back to full recovery mode? Just wondering if this anyhow matters..
# Is there a way to do an effective db shrink w/o putting the db to simple recovery mode beforehand?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the log files do not ge truncated after the backup is done
Sure they do. It is just that your definition of "truncated" does not match the definition covered in SQL Server's BOL.
# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.
Correct. They thankfully got rid of the TRUNCATE_ONLY keyword. It got too many people into trouble (see Brent's blog on the subject How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012)
Sure they do. It is just that your definition of "truncated" does not match the definition covered in SQL Server's BOL.
# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.
Correct. They thankfully got rid of the TRUNCATE_ONLY keyword. It got too many people into trouble (see Brent's blog on the subject How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks again to all for your valuable comments.
My main concern here is that when I look at my log file, its size is too big to store so small amount of logs. When I run a script to calculate it's usage, i.e. comparing the physical size of logs to the space the log file occupies on HDD I end up with usage of less than 1%
I don't really care about the way of decreasing the log file size, just don't want to store a shot of vodka in a barrel. Why would I need to consume a disk space for nothing?
My main concern here is that when I look at my log file, its size is too big to store so small amount of logs. When I run a script to calculate it's usage, i.e. comparing the physical size of logs to the space the log file occupies on HDD I end up with usage of less than 1%
I don't really care about the way of decreasing the log file size, just don't want to store a shot of vodka in a barrel. Why would I need to consume a disk space for nothing?
Hi,
Just a thought - if you are having to shrink the transaction log because you are not doing transaction log backups, then switch to simple recovery model.
Its a standard good practice to change model to simple recovery mode, so new databases are in simple recovery.
Regards
David
Just a thought - if you are having to shrink the transaction log because you are not doing transaction log backups, then switch to simple recovery model.
Its a standard good practice to change model to simple recovery mode, so new databases are in simple recovery.
Regards
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi AC,
Completely agree ...
Regards
David
Completely agree ...
Regards
David
ASKER
thanks again for all your comments
Any reason why you are using sql server 2000 level 80 ?
What is this DB used for ?
Is it an in house application or off the shelf..?
Are the log files getting truncated after a successful backup ?