June 18, 2014 at 8:34 am
Hi Experts,
We have a SQL Server 2008 Database which we use for OLAP purpose (DWH). Its quite a critical server for our business and runs 24*7. Just wanted to know if its a good idea to restart the SQL server services or restart the server itself once in a month or once in two weeks?
At the moment server runs all the time and its never restarted. so wanted to know if a planned restart a good maintenance routine?
June 18, 2014 at 8:47 am
Planned restarts and planned maintenance is a good thing.
There are OS patches and SQL patches that should be getting rolled out to the server that will typically require a restart.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 9:51 am
You don't want to reboot it for no reason though. Sure, reboot for OS patches or stuff like that, but rebooting just for the sake of rebooting, no.
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
June 18, 2014 at 10:09 am
GilaMonster (6/18/2014)
You don't want to reboot it for no reason though. Sure, reboot for OS patches or stuff like that, but rebooting just for the sake of rebooting, no.
Agreed.
If you have 365 days since your last reboot - you have probably gone too long because there have been a ton of critical patches.
But if there are no patches and your system is stable - there is no need to reboot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 10:10 am
Yeah, just piling on, we scheduled an annual restart for all machines, regardless of patching, etc. but other than that only restarted machines when there was a necessary patch. There's no reason at all to just reboot the machine every two weeks (there used to be, SQL Server 6.5 had so many memory leaks it was necessary, but not the modern tools).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2014 at 10:56 am
The following scenarios in general I would recommend to reboot the machine.
1.After the SQL Server patches and Service Pack updates.
2.Windows Quarterly Patches or security updates.
3.Any server maintenance tasks.
4.Firmware upgrades and application bugs/patches.
5.In case if any database rollback process takes forever.
Other than you don’t need to reboot the machine. SQL Server 2008 is pretty stable and clears up the memory automatically (Memory management)
June 18, 2014 at 10:59 am
Hema kumar-337490 (6/18/2014)
The following scenarios in general I would recommend to reboot the machine.1.After the SQL Server patches and Service Pack updates.
2.Windows Quarterly Patches or security updates.
3.Any server maintenance tasks.
4.Firmware upgrades and application bugs/patches.
5.In case if any database rollback process takes forever.
Other than you don’t need to reboot the machine. SQL Server 2008 is pretty stable and clears up the memory automatically (Memory management)
erm, I wouldn't reboot due to a long running rollback. That is a recipe for a bad day.
edit: fixed typo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 11:28 am
Hema kumar-337490 (6/18/2014)
5.In case if any database rollback process takes forever.
Because it's better to have the rollback take forever with the DB unavailable than for the rollback to run while the DB is available and usable? Interesting idea.
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
June 19, 2014 at 7:18 pm
5.In case if any database rollback process takes forever.
Not for the Prod env of course. But for Dev or UAT, when Database can be easily restored from the backup, it is better to restart the services and start with fresh data, rather than waiting for the rogue transaction to complete its rollback.( you have to delete the underlying data files thought when services are off)
June 20, 2014 at 7:47 am
barsuk (6/19/2014)
5.In case if any database rollback process takes forever.
Not for the Prod env of course. But for Dev or UAT, when Database can be easily restored from the backup, it is better to restart the services and start with fresh data, rather than waiting for the rogue transaction to complete its rollback.( you have to delete the underlying data files thought when services are off)
Yes because having developers down for hours is a good idea?
Or is it because it is better to just give them empty databases since you deleted their data files?
In the event of a rollback, this is just bad advice.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 20, 2014 at 8:19 pm
It happens rarely in UAT, when different teams are applying their scripts automatically overnight, without knowing what the other team is doing or for any other reason.
So in those cases it results in blocking. And killing it ends up in rollback.
Since we always have the latest backup, restarting the services and restoring the latest backup takes literally 15-30 minutes, depending on the size of the databases and it is much quicker then waiting rollback to finish for the other 6 hours or so.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply