November 20, 2011 at 3:29 pm
People,
I need a help. A have to create a maintenance plan with next steps:
1. Set recovery model to simple
2. Shrink database
3. Full backup
4. Set recovery model to full.
Please help. I really need it.
Thanks in advance.
November 20, 2011 at 4:03 pm
imilikic (11/20/2011)
People,I need a help. A have to create a maintenance plan with next steps:
1. Set recovery model to simple
2. Shrink database
3. Full backup
4. Set recovery model to full.
Please help. I really need it.
Thanks in advance.
I really don't think you need what you think you need. Sounds like your transaction log file keeps growing and you don't know how to stop that except by switching to/from Simple.
If this is true, a better solution is to backup the transaction log file regularily (every hour or two maybe?). This action will 'empty' most of the tlog file and allow the same space to be reused until the next tlog backup. You still need to backup your transaction log even if you do a full backup. The tlog file should be large enough to neither need to shrink nor grow.
In most cases, you should never shrink the database or transaction log; especially regularly.
If you insist that you need the above, I would love to hear your detailed reasons for that decision.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 20, 2011 at 9:53 pm
I really don't think you need what you think you need. Sounds like your transaction log file keeps growing and you don't know how to stop that except by switching to/from Simple.
If this is true, a better solution is to backup the transaction log file regularily (every hour or two maybe?). This action will 'empty' most of the tlog file and allow the same space to be reused until the next tlog backup. You still need to backup your transaction log even if you do a full backup. The tlog file should be large enough to neither need to shrink nor grow.
In most cases, you should never shrink the database or transaction log; especially regularly.
If you insist that you need the above, I would love to hear your detailed reasons for that decision.
That's what we do in our environment - backup trans logs every two hrs with a rolling 24 hr retention period (to cover time between full backups). We do find, however, that with some databases we still have trans log growth and have to go in and shrink just the trans log data file so it resets to the default size. We're also finding that even though we do regular maintenance on our databases themselves we're going to have to figure out some sort of footprint control - even though we have a custom stored procedure to delete temp data from the databases, we do full backups regularly, we do index reorgs (weekly) and rebuilds (monthly or semi-monthly) regularly I'm still finding some databases with more than 10-20% free space in them (not including the trans logs).
The only thing I can think to do would be to shrink the whole database then do a reindex (to fix the index fragmentation issue from the shrink). Do you have a better idea?
November 21, 2011 at 12:14 am
Please, please don't shrink your database. It sounds like you're not properly managing your logs.
Please read through this - 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 recoverability
November 21, 2011 at 12:20 am
Chris Metzger (11/20/2011)
That's what we do in our environment - backup trans logs every two hrs with a rolling 24 hr retention period (to cover time between full backups). We do find, however, that with some databases we still have trans log growth and have to go in and shrink just the trans log data file so it resets to the default size.
Why do you want the logs the default size? A log growing is an indication that the currently set size is too small, not an indication that a shrink is needed. Growing of a log is not a quick process, the new log has to be zeroed out and any data modifications in progress will have to wait for that to complete.
Either leave the logs at the larger size or consider making your log backups more frequent as that will usually keep the log smaller (unless it's growth is caused by a single huge transaction like an index rebuild)
The only thing I can think to do would be to shrink the whole database then do a reindex (to fix the index fragmentation issue from the shrink). Do you have a better idea?
Leave them alone? Free space in a DB is not harmful and SQL needs free space in a database to work properly (especially around index rebuilds). 10-20% free in a DB is usually what I want to see
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 12:33 am
Yeah well I don't have the disk space to allow 150+ DBs to have log files that just grow and grow (I have some that end up 2 or 3 times the size of the database itself just during normal daily operations not to mention during our conversion process where they could be 6 to 10 times the size of the database) - I have to actively manage them because I don't have a choice when I pay per GB for storage on my hosted/hosting platform (it's a waste to allow 25 - 100GB log files). In addition, VMware vCloud doesn't allow you to increase the size of a disk once it's created so I have to work around that and add a data and log disk set to the VM to keep adding instances to my SQL Servers.
Same applies to the databases - it's one thing to allow them to retain some space for working data (our app writes a lot of temp data that we clear out using custom stored procedures or on-demand when needed) but allowing unmanaged growth isn't an option - period. And now I have a new issue to deal with - the developers wrote a new module for our app that allows whole document storage inside of the application database so I'll have to see how that is going to affect growth as clients add that functionality.
There has to be a better way to actively manage databases - aren't there utilities out there that can show a snapshot of the SQL subsystem and all instances as well as all databases and their stats? Or doesn't anyone actively manage their footprint anymore?
November 21, 2011 at 12:42 am
sys.databases, sys.database_files, sp_spaceused
First 2 for one instance, last is per-database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 12:54 am
GilaMonster (11/21/2011)
sys.databases, sys.database_files, sp_spaceusedFirst 2 for one instance, last is per-database.
What about using something like RedGate's SQL Monitor? I have an off-shore resource that I can task with these things but I'd rather automate as much as I can (like our maintenance plans have 8 subplans in them now) but it has to be active and not passive. If I can catch a trend through monitoring then I could include it in our standard maintenance plan for all instances (ie. we switch trans log backups from every 2 hours to every hour). Thoughts?
November 21, 2011 at 1:05 am
3rd party monitoring tools are typically how people do things, but I get yelled at by posters when I refer them to solutions that require money instead of built-in T-SQL options. 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 1:18 am
LOL! While I appreciate that, cost is not my concern - performance is for sure. So if it costs me money to get something that will do the job for me in half the time then so be it - my time is worth more than the $1k per year cost to buy a piece of software (and it's certainly cheaper to buy something and be proactive than hear from clients when something isn't working right and have to fix it after the fact). Cool - then I was right to already recommend that package to my boss. As soon as I get back from vaca I'm going to finish my platform and then spin up a new management VM on my management vLAN and trial those tools from Red Gate as those look like they are pretty awesome for what I need. While I understand shrinking a database can be bad if you don't allow for room to work as well as compensate for the index fragmentation sometimes it has to be done to manage the footprint of the platform as a whole (or like if we have to transport a database to Development for troubleshooting). Fortunately our Developers are brill and wrote in a procedure that when we apply updates to our app and it updates the databases, the updater drops all indexes and reindexes the entire database (over 1k tables per database) - and we release updates about every month or every other month so that's almost built-in maintenance for me.
Thanks!! Glad I found this board - it's been ever so helpful so far.
November 21, 2011 at 1:24 am
cost is not my concern - performance is for sure.
I wish my company believe in this policy / thoughts. 😀
November 21, 2011 at 1:44 am
Jim ,
Thanks for your quick response.
I agree with you that this is not maybe what I need. I am new in this field.
What you should know is that on a daily basis I need to work automatically back up but before that to reduce the log file that grows quite every day.
It would mean a lot to me if I can get step by step explanation as I'm doing this for the first time.
Thanks a lot.
November 21, 2011 at 2:08 am
Dev (11/21/2011)
cost is not my concern - performance is for sure.
I wish my company believe in this policy / thoughts. 😀
I would blow a gasket if they said no to the cost of that software package - we're looking at around $40k/mo for our hosted/hosting platform (which is what I manage) on a vCloud architecture. It's one thing to say no to the licenses for PowerArchiver (which has been a challenge to get when you can use some free crappy utility) but another when it impacts revenue for/from the platform. I just wish I could finish getting the thing built!
Sorry for hijacking this thread.
November 21, 2011 at 2:17 am
imilikic (11/21/2011)
Jim ,Thanks for your quick response.
I agree with you that this is not maybe what I need. I am new in this field.
What you should know is that on a daily basis I need to work automatically back up but before that to reduce the log file that grows quite every day.
It would mean a lot to me if I can get step by step explanation as I'm doing this for the first time.
Thanks a lot.
There are a lot of good resources out there to help you setup a SQL maintenance plan for automatic transaction log backups. This is really what you probably should be doing so it will keep your trans log in check every few hours (based on need - you can adjust it to happen more or less frequently over time as you see how well it is working for you). Even the SQL Books Online inside SQL has good info and step-by-step instructions for a lot of stuff.
For example, my trans logs grow regularly. I have SQL backup just the trans logs every 2 hours. Then as part of the maintenance plan (another subplan inside - it allows you to have more than one task in a plan on a different schedule) I check for files ending in TRN in a particular folder (mine are separated at the root into folders per instance which is per client) and delete anything older than 24 hours - and it runs right behind the trans log backup so I never have more than 24 hours of trans log backups sitting on my drive in any one folder. Then once a day I have another subplan that runs full backups and a matching subplan that keeps the full backups in check so I only retain 2 days' worth of those (so I have 2 days' worth of what's called "hot" backups because they're on the disk and not on tape and I could restore them at any moment). In addition, I have another subplan that runs weekly to reorganize my indexes - something that should be done on a regular basis too.
If you are just starting out and don't know anything about SQL at all - like what is a maintenance plan, how do I set one up, etc., it may make better sense for you to get a SQL for Beginners guide and read that - there are some really good ones available (trust me, we've all had to start somewhere - I'm no expert but I'm no longer a beginner either). Someone on here may have a specific pointer for you as to what may be a good title to start with or you could just peruse your local bookstore and see what they have. HTH
November 21, 2011 at 7:10 am
imilikic (11/21/2011)
What you should know is that on a daily basis I need to work automatically back up but before that to reduce the log file that grows quite every day.It would mean a lot to me if I can get step by step explanation as I'm doing this for the first time.
Probably means you're not maintaining the log properly. Please read through this - 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 recoverability
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply