| Author |
 Topic  |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
 Posted - 03 March 2005 : 11:19:15
|
Just curious if any of the server admins here have tips for other admins regarding Sql Server maintainence.
Apart from the regular stuff e.g. backups, etc. is there any non routine stuff you do or third party software you use?
I admin a busy sql server with about 1500 databases and everything runs smoothly (thank God). I have time on my hands at the moment and am looking at doing the following;
1. Some of the transaction logs are 5 years old and are GB's in size. Rather than shrinking them (which would only save a little space) I'm thinking writing a script which will detach the log, then delete it and create a new log file. I don't think I will ever have to restore a db to a particular point in time 5 years ago. As my backups are daily I would only lose a days data worst case scenario, which is acceptable to me.
2. Analyse the most cpu intensive queries with a view to optimising them. Anyone ever tried this software - http://www.quest.com/quest_central_for_sql_server/index.asp
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2005 : 11:25:58
|
| why are your transaction logs that big, if you are doing regular backups and you have your db's set to simple recovery (recomended) your transaction logs should never get very big as they only contain data since the last backup. The transaction log for Snitz never grows above 20Mb |
 |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2005 : 14:47:18
|
| I would change them all to simple, do a backup, then shrink all the transaction logs, when that is done I would defrag your drive. |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 March 2005 : 06:44:12
|
Change your Model DB to the Simple recovery mode as well, that way future databases (unless overridden) will be created as simple as well.
Another tip would be to frequent sqlteam.com there are some excellant articles and normally a pretty helpful community. |
Kiwihosting.Net - The Forum Hosting Specialists
|
Edited by - Gremlin on 05 March 2005 06:45:07 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 05 March 2005 : 10:03:02
|
I agree with Gremlin... anytime I have a question about a query or any sql question I can 99% of the time search their Snitz Forums  and find what I am looking for. If I can not find it, I can usually get a answer in a day. Like I said I have to agree... what a great site. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 05 March 2005 : 14:31:11
|
quote:
Because logfiles are updated sequentially whereas updates to dbs usually aren't.
It also means that your drives do less work because they can write logs and read data at the same time  |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 March 2005 : 19:10:25
|
| Though you do need to be mindful of your drive configuration before doing so, if you have a server with older PATA drives and they're on the same channel the efficiency gain may not be as great as if they were on seperate channels or using newer SATA drives. SCSI is of course a whole different kettle of fish and should ideally be whats used in any half decent server anyway. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
fetch
Starting Member
46 Posts |
Posted - 03 July 2005 : 23:38:17
|
quote:
Originally posted by HuwR
why are your transaction logs that big, if you are doing regular backups and you have your db's set to simple recovery (recomended) your transaction logs should never get very big as they only contain data since the last backup. The transaction log for Snitz never grows above 20Mb
I converted my Snitz Forum from Access to SQL, and for some reason the logs steadily grow at a rate of several dozen megs per day. The database is set to autoshrink and actually has a maintanence task to shrink them every day, but still no dice. Once every week or two I end up having to detach, delete, and re-attach. Any ideas as to why I have this problem? I'd love to be able to completely ditch that particular bit of maintainence... thanks! |
http://www.mhsaa.org/4rum/forum/
|
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
| |
Topic |
|
Topic Locked
