December 20, 2012 at 1:54 pm
Basics: I'm running a SQL Server 2008 R2
I am trying to run a SQL job or maintenance plan on the startup (restart) of a SQL Server.
I’m trying to automate a Database Shrink job, which has to have exclusive access to the database to run. Realistically, the only time this happens is when the server is restarted, so I’m looking for a way to run a job on restart.
As always, any help is greatly appreciated
Thank you,
David92595
December 20, 2012 at 2:16 pm
David92595 (12/20/2012)
Basics: I'm running a SQL Server 2008 R2I am trying to run a SQL job or maintenance plan on the startup (restart) of a SQL Server.
I’m trying to automate a Database Shrink job, which has to have exclusive access to the database to run. Realistically, the only time this happens is when the server is restarted, so I’m looking for a way to run a job on restart.
As always, any help is greatly appreciated
Thank you,
David92595
Why do you want to shrink your database on startup? It will greatly increase fragmentation and reduce performance.
Please read and understand the ramifications of what shrinking your database does.
Here is one article on the topic. http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/[/url]
There are dozens and dozens of articles detailing exactly why you should not shrink your database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2012 at 8:09 pm
Don't shrink your databases regularly. If you need to do this, it's a rare, one-time event as Sean mentioned.
December 20, 2012 at 8:13 pm
I agree strongly with the above but if you still want to go ahead with it I wouldn't run it on startup but rather put the database in SINGLE_USER mode first so you can get exclusive access to it, this way you can plan the time to do it and not if your prod server crashes and starts trying to shrink db's during business hours.
More info here:
December 21, 2012 at 5:27 am
Sean Lange (12/20/2012)
Here is one article on the topic. http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/[/url]
My reflex was to post this same link which is my favorite resource to pass along on these types of posts!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 10:42 am
Thank you all for your suggestions. I have decided not to run this on start up, and I understand all of your concerns as to running it.
I inharited a server that had the mdb and the log files on the same drive and over time and negligence on my part the log files took up all available space on the drive, bring our databse to a halt.
I have already move the log files to a different drive, and am still open to suggesions. But this is the most direct and logical way I have found to insure this does not happen again.
Thank you for all of your help,
David92595
December 21, 2012 at 10:50 am
If your logs grew once, after shrinking them they will likely want to regrow again to the same size due to the normal workload on the server. Adding more disk or changing log management strategies are the only way to really deal with the issue. Please start here:
Managing Transaction Logs By Gail Shaw, 2012/01/03 (first published: 2008/10/31)[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 11:42 am
Is this database in full recovery mode?
.
December 21, 2012 at 12:16 pm
@fluffydeadangel
yes it is in Fully Recovery mode
We run two backups a day, one as I'm leaving the office and one at midnight.
December 21, 2012 at 12:37 pm
we run a differential every 4 hours on schedule, a full back up every night at midnight and a transactional log backup every hour on the hour. Is there a reason it's not automated? (rather, is it automated?) Such as space issues or lack of san access? We have at least 2 servers of our 140 that if it's not set on hourly transaction log backups, the servers would fill each of their respective 500GB transaction log drive.
.
December 21, 2012 at 12:47 pm
yes, we are running in comparison to most sql database a rather small and staic database. I was told that a full backup once a day was all that was necessary...I used to run differential backups, and transational backups but was told they are both over kill for our database. :ermm:
I run a manual backup of our database when I leave each day to my jump drive to have a copy outside of the office should the building catch fire, flood, ect. and the midnight backup is automated.
December 21, 2012 at 12:52 pm
i can see that, you automate your full backup and you take a second one. if you're taking one, once a day anyways, why not change your automated one to your CoB time and take that one instead of having two if one is good enough? and how often do you run your transactional backups? the more often it is, the less dataloss you have in bad situations. (though more tedious restoring becomes)
.
December 21, 2012 at 1:06 pm
David92595 (12/21/2012)
yes, we are running in comparison to most sql database a rather small and staic database. I was told that a full backup once a day was all that was necessary...I used to run differential backups, and transational backups but was told they are both over kill for our database. :ermm:I run a manual backup of our database when I leave each day to my jump drive to have a copy outside of the office should the building catch fire, flood, ect. and the midnight backup is automated.
If your database is using the full recovery model (or even bulk logged) you need to run transaction log backups regularly to manage the growth of the transaction log. Full and differential backups do not truncate (mark VLF's as reusuable) the transaction log.
December 21, 2012 at 1:09 pm
David92595 (12/21/2012)
yes, we are running in comparison to most sql database a rather small and staic database. I was told that a full backup once a day was all that was necessary...I used to run differential backups, and transational backups but was told they are both over kill for our database. :ermm:I run a manual backup of our database when I leave each day to my jump drive to have a copy outside of the office should the building catch fire, flood, ect. and the midnight backup is automated.
There's your issue. You MUST run Tran Log backups to release the active portion of the log. FULL (nor DIff for that matter) are good enough. With your backup strategy what you are saying is that you are OK with losing up to ~20 hours of data (time diff between midnight backup and CoB FULL backup). If that is true then change the recovery mode to SIMPLE and shrink the log to an optimal size. If not, then start doing tran log backups immediately and schedule to run at a frequency consistent with your tolerable data loss (and get them off the server where the DB resides). Once tran log backups are set then you can look to shrink your log one time to reclaim some of the space and your tran log backups should kleep your log at a stable size.
Please read the article I posted to before in its entirety.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 1:12 pm
@SSCoach
Can you please provide a resource that shows that full backups do not truncate transation logs? I was under the impression that a full backup does truncate the transation logs.
It's not that I don't believe you, but I will need some evidence to provide to my superior if I am going to start using transation log backups...
Thank you,
David92595
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply