| Author |
Topic |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2010-08-10 : 16:48:47
|
| I'm using Tara Kizer's backup script on a SQL Server 2005 9.00.3073 machine that we're using for Sharepoint. I only modified the name of the Stored Procedure, everything else I left the same.My problem is the .ldf file for the main Sharepoint Content database is 2.5 times the size of the .mdf file and doesn't seem to be stopping. I've gone through the jobs and the databases to see if I can find the reason the transaction log isn't truncating itself, but I must be missing something because I just don't see it and I'm concerned about manually shrinking without making sure that's what needs to happen.I have set up 2 jobs, one to do full backups of all databases that runs once a week. The other is a tlog backup of all databases that runs hourly. My full backups are working, the tlog backups are working, but the log file just keeps growing.I'm using the same script on a different machine and everything looks good on that one.If anyone has any ideas about how to cut the ldf file down, that would be great, because it's taking up a tremendous amount of HD space.Thanks |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-11 : 04:00:48
|
| What's the value of log_reuse_wait_desc in sys.databases for this DB?--Gail ShawSQL Server MVP |
 |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2010-08-11 : 09:39:45
|
| For this database, the desc is set to NOTHING. (the msdb is set to CHECKPOINT, and a config db and a report server temp db are set to LOG_BACKUP, everything else is NOTHING as well).We occasionally upload some large files to the database, but the mdf file is 20GB, and the ldf file is 46GB and that just seems way out of proportion. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-11 : 11:03:35
|
| Check during the loads. My bet - doing lots in one transaction--Gail ShawSQL Server MVP |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2010-08-11 : 17:03:34
|
| By large files, I meant a couple hundred MB's, maybe a GB of data (so not that big I guess). This would be maybe once a week that someone would move that much into/out of the database, for the most part, it's small text updates to lists, maybe a few picure attachments, or a PowerPoint presentation. Nowhere near 45GB of data, I don't think we've ever had that much data in the database to require a log file that large. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2010-08-13 : 11:56:45
|
| I threw your name out there as the creator of the script 1, because I didn't want anyone to get on the wrong track with thinking my backups were the issue and 2, because it's awesome and you deserve the credit for it .I'll bump up the tlog backup to every 15 min and change the full backup to nightly.Auto-shrink is off, so I'm assuming I will have to manually shrink the log once I see where the DBCC SQLPERF(logspace) % sits once the jobs start their new schedule? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
LDF file growth