Stored Procedure - Shrink with truncate, every 30 minutes
-
Hello all,
First post here. I have been tasked with creating a daily backup of our MSSQL server each night, and with running a task every 30 minutes to shrink the log files, with truncate. Everything I have read on these topics is straightforward. The only thing I can't find any information on is running a task every 30 minutes on SQL server (We have MSSQL Server 2005, with SQL management studio ). I'm much more familiar with cron jobs in Linux, is there anything similar for SQL server?
Kindest regards,
AG
-
Use BOL (Books On Line -SQL Help File)
Start with gaining an understanding of database and log back ups and their effect on logfile size.
And then look at SQL Agents at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/51352afc-a0a4-428b-8985-f9e58bb57c31.htm
-
The first thing you need to be looking into is why are you being asked to do this? Truncating the logs and shrinking the database every 30 minutes is not something you should be doing on a production database. And if this isn't a production database, then you should probably just set the database to simple recovery mode which will basically do this for you.
And in SQL 2008, they've deprecated the ability to truncate the log so you have to set it to simple recovery.
Please post more details about why and maybe we'll be better informed on what to recommend to you.
-
You can schedule jobs in SQL Server by using SQL Agent. You can set a variety of intervals and rules on the jobs, and they can be multiple or single step jobs. That's how you'd set up the schedule for the shrink.
HOWEVER, don't do it. Again, do not set this job up.
Doing this eliminates the ability to do point-in-time restores for your database. That means you will have to revert all data to the last full/diff backup if you lose database integrity for any reason. Keeping log backups standardly will allow you to recover, in the vast majority of cases, to the last successful transaction in the database, even if the last full backup was a week ago and the files were corrupted five minutes ago.
If you don't need that ability, for whatever reason, then set the database to Simple recovery mode, and you won't have to truncate the log file. Eliminates the need for that completely.
In either case, automatically shrinking either the database or the log file has the primary result of increasing file fragmentation, which means the whole server runs slower, without any actual benefit of any sort.
A properly set up log file won't grow out of control anyway. Shrinking it usually just means it'll have to grow again. That means that (a) transactions will be delayed as the file grows/shrinks, and (b) the file will be fragmented all over the hard drive.
Does that make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
agriggs 11853 (10/21/2009)
running a task every 30 minutes to shrink the log files, with truncate.Bad idea. Exceedingly bad idea. Please go to the person who told you to do this and ask them why. Read this article and ask the person who gave you this task to also read it. Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for the replies, here's the background. We implemented a new ERP in March and the log files had grown to 124GB by this week. This is a production database. (Disclaimer...SQL server management is not our forte) The ERP provider suggested we do a daily backup and shrink/truncate every 30 minutes. That's it in a nutshell.
After reading the replies, a couple of thoughts/questions:
1. We are already doing incremental backups of all servers on a daily basis, would the consensus agreement be that this would suffice for the daily backup?
2. Shrink/truncate every 30 minutes seems like overkill. It took 6 months to get to 124GB, wouldn't a daily process be sufficient? Or is simple recovery sufficient enough?
Given my obvious lack of knowledge in this area, any practical/real world suggestions/advice would be very much appreciated.
-
agriggs 11853 (10/21/2009)
1. We are already doing incremental backups of all servers on a daily basis, would the consensus agreement be that this would suffice for the daily backup?Incremental backups? How exactly are you doing 'incremental' backups?
2. Shrink/truncate every 30 minutes seems like overkill. It took 6 months to get to 124GB, wouldn't a daily process be sufficient? Or is simple recovery sufficient enough?
It's not the growth that's the point here. Do you need to be able to restore that DB to the point it failed? If say your backups are 8pm and the DB fails the next day at 6pm, is losing 22 hours of data acceptable? This is the question that you need to ask.
Read the article that I recommended. It's a decent covering of the basics of managing a transaction log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What about transaction log backups? Are you doing any? If your answer is yes, my next question will be "How often are you doing the transaction log backups?"
-
Since ERP data is generally pretty crucial, I'd be inclined towards daily full backups and hourly or half-hourly log backups. Set that up in scheduled jobs in SQL Agent, and you should be good to go, and your log file won't grow so spectacularly in the future.
After a couple of days, check out how much free space there is in the log file (you can do that by going to the Shrink Files option from right-clicking the database) and you should be able to reduce it to a gig or two and it'll probably stay there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Shrink/truncate every 30 minutes seems like overkill. It took 6 months to get to 124GB, wouldn't a daily process be sufficient? Or is simple recovery sufficient enough?
If you have setup frequent Transaction Logs (I would prefer every Quarter-Hour) then you should be not required to Shrink /Truncate..
Since it is and ERP and in Production, Simple Recovery Model should not be used (Transaction Logs would not be available in Simple Recovery)
Frequent autogrowth of the database files should be avoided as much as possible...
As Gail mentioned, Incremental backups does not exist in SQL Server, you must have been confused with the Differential Backups. (If I am not wrong, Oracle has Incremental Backups)
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply