Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DarthTong
Starting Member
1 Post |
 Posted - 2009-05-13 : 09:50:29
|
| Hi all,Not being a db admin I'm strugling somewhat with my latest project. We have a database (MS SQL 2005 Standard, full remote desktop access) which gathers a lot of data from some of our websites. I recently looked at the database and transaction log size and was a little disturbed to find the trans log was 12GB! The db was 150MB, as expected. Most of the queries make use of the rollback feature of MS SQL 2005, so am wondering if this is causing the giant file size.What made me look at the size was the SQL queries started to timeout every hour or so, and I think that was caused by the Autogrow settings on the database / transaction log. The database currently says it has 0.75MB of space left, and has the default Autogrowth setting of 'By 1MB, unrestricted growth'. The transaction log's Autogrowth setting is the default 'By 10 percent, restricted growth to 2097152 MB'.So a few questions:Do I need the transaction log to keep all this data, can I clean it up every night when the backups are done?How can I stop queries timing out everytime the database has to grow?What Autogrowth / backup options would be good for an ever expanding database?Any help on this matter would be very helpful!Cheers in advance! |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-05-14 : 16:35:01
|
| 1. What is the business tolerance for data loss? Is this a production database? Do you need point-in-time recovery? Can you get away with SIMPLE recovery mode, and regular FULL database backups? 2. If not, implement a backup strategy using full and log backups regularly. http://msdn.microsoft.com/en-us/library/ms191239(SQL.90).aspx3. Grow the files out-of-hours, using ALTER DATABASE ... MODIFY FILE size = 500MB. Do you know how much the database will grow in the next 3/6/12 months? How much free space do you have?4. Use the AUTOGROW option as a fail-safe only!5. Look at the fragmentation of the database files. |
 |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-05-15 : 17:00:51
|
| The 10% file growth on your transaction will do just that. 1.2gb of space will take quite a while if your not using beefy hardware. Reconfigure the autogrowth to a set number IE 3-5mb. that will happen more frequently but should not be as stressful on the system. Hopefully resolving the timeouts. |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-05-19 : 00:40:22
|
| Yes, even if a query is rolled back it is written to the transaction log. SQL server does not know if a query is going to finish or not until it receives the commit or rollback, so your open transaction goes into the t-log as well as any inserts but the data does not actually get written to disk until the check point following a commit. If the transaction is rolled back SQL just throws out the dirty pages.To stop queries from timing out use a fixed growth size, 100mb, 200mb, or whatever your system can handle without a significant pause in transactions. Setting the number too small can lead to fragmentation and other performance problems, it can also cause query timeouts if you have a single huge insert as SQL will have to grow the file many hundreds of times during the transaction.For backup options, it sounds like you have atleast a daily full backup. From that you should perform a regular transaction log backup which will truncate the log file, as full backups do not truncate log files. You can run transaction log backups during business hours with very very little impact on performance.Also, as suggested you may want to consider other recovery models, such as simple depending on your data recovery requirements. Can the business afford to lose 24 hours worth of data? From the sound of it, yes as you are only currently doing daily fulls. If that is the case set it to simple mode, do a backup log [database] with truncate_only and then go in and shrink all the log files. For the database file itself, I high recommend setting a fixed growth of maybe 50-100mb, leaving the default 10% is the worst thing you can do and I often wonder why the SQL team chose to set that as a default. |
|
|
|
|
|
|
|
|
Database / Transaction Log AutoGrow