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 |
|
10Dawg
Starting Member
46 Posts |
 Posted - 2012-01-17 : 10:39:05
|
| I have a query that shrinks a log file down to 1GB. I want it run automatically after the nightly backups. How? Make it a stored procedure and add it to my maintenance plan? How do I change a query into a stored procedure?If not, how else can I make it part of my nightly maintenance plan? Is there something already in place that I can just add? (Note that the query changes the backup type from full to simple and then back again after the shrink)Thanks in advance.10Dawg |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2012-01-17 : 10:58:22
|
| O yes, I forgot to mention it was 2008. I will do the links. Thanks.10Dawg |
 |
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2012-01-17 : 11:40:55
|
| OK then let's back up a second (no pun intended). The problem is the logs files continue to grow. Our environment WAS NOT MY DESIGN, I want to say that up front. I inherited it. The Backup, BCPPath, dtShare, FTData, LDF, MDF do not go to the default location, they go a dedicated (nothing else on it) 3TB server. Right now I have 1.19TB free of 2.99TB on that server. I have 20 dbs with 12-15 active daily at any one point. The nature of the business is that a db may go dormant for a month or 2 and then become active again. Left unattended (except for zipping the DB Backups every morning and storing them elsewhere) That 1.19TB will be full in 2-3 weeks. I would like to do transactional backups hourly on the db that are active but I don't have the room. So I do a nightly backup (Shrink db, Rebuild Index, Check db, Backup db, in that order). But that doesn't address the log files that continue to grow unabated. So the bigger question is, How can I safely maintain this environment?10Dawg |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-18 : 03:46:50
|
"I would like to do transactional backups hourly on the db that are active but I don't have the room."So you don't need, or want, any log backups?Change the Recovery Model from FULL to SIMPLE and then the log will automatically clear (i.e. become available for reuse, NOT Shrink) after each transaction is committed - if you have HUGE transactions then the Log file will still grow to accommodate the largest one.(I've slightly oversimplified it, but that is the gist of it)In SIMPLE Recovery Model you will have NO ability to recover to a point-in-time, so you will only be able to restore to your last full backup - but by the sounds of it you are in that position already?Note that if you repeatedly shrink the files you cause both physical and logical fragmentation which adversely impact performance. In particular if you rebuild an index, to improve performance, and then SHRINK the database the index will no longer be contiguous and will be fragmented within the database file - the very thing that the index rebuild just fixed  |
|
|
|
|
|
|
|
|
add query to maintenance plan