Adam
New Member
Posts:3
 |
| 28 Nov 2009 06:59 PM |
|
Hi
First thanks for the excellent and free Scheduled SQL Jobs module.
I noticed a DNN Performance TuneUp module updated today - http://www.snowcovered.com/snowcovered2/Default.aspx?tabid=242&PackageID=16256 and it got me thinking about whether a similar script should be added to Scheduled SQL Jobs.
Searching I see there are plenty of scripts floating around such as:
http://www.sqlmag.com/Articles/ArticleID/96059/96059.html
http://weblogs.asp.net/markwisecarver/archive/2009/06/24/sql-server-index-defrag-script.aspx
So my questions are:
1. Do you see a Defrag/Reindex as something that needs to be run for your typical small business websites?
2. Do you have a script that you can recommend?
3. Would you consider adding one into the next release?
Cheers
Adam
|
|
|
|
|
Mitchel Sellers
Site Moderator
Basic Member
Posts:328
|
| 29 Nov 2009 06:37 PM |
|
Adam.
1.) Proper database maintenance is something that is needed for all databases. However, I find that many smaller sites, just do not have the traffic to result in high fragmentation, but it is still an important process, the frequency is just a lot less.
2.) The one you posted from SQL Server Magazine is actually a pretty good one. I personally do a lot of that manually when I am administering a database.
3.) Yes, I will, and for the next release you will be able to add your own scheduled jobs. (It is 1/2 way supported already....just no interface for it in the current version) |
|
-Mitchel Sellers, Microsoft C# MVP, MCITP
CEO
IowaComputerGurus Inc!
Visit my blog for helpful technical articles
For recommended products and services view the links to the right. |
|
|
Adam
New Member
Posts:3
|
| 29 Nov 2009 07:15 PM |
|
Mitchel
Thanks for the feedback. I was surprised to see a number of tables in some of our relativity new smaller sites such as Module Definitions 80% fragmented so it does appear to affect even smaller sites. I had an issue running the SQL Server Magazine script, it started fine then said i don't have enough permissions even after specifying a database and table so this may need to be considered when you implement the script. We are running a VDS so have full admin control to the server so I'll have to investigate.
I look forward tot he next release.
Cheers
Adam
|
|
|
|
|
Mitchel Sellers
Site Moderator
Basic Member
Posts:328
|
| 29 Nov 2009 07:22 PM |
|
Adam,
What permissions does the user have when you are executing the script? I believe that you need DBO to query from the management views. |
|
-Mitchel Sellers, Microsoft C# MVP, MCITP
CEO
IowaComputerGurus Inc!
Visit my blog for helpful technical articles
For recommended products and services view the links to the right. |
|
|
Adam
New Member
Posts:3
|
| 06 Dec 2009 04:11 AM |
|
Sorry i missed the response. I ran the SQLMag one again and it worked fine this time. I noticed that the rows which indicated fragmentation don't appear to reduce after running the script such as:
ModuleControls IX_ModuleControls 2 1 87.5 0 NONCLUSTERED Y
TabModules PK_TabModules 1 1 66.6666666666667 0 CLUSTERED Y Y
EventLogTypes PK_EventLogTypes 1 1 75 0 CLUSTERED Y Y
then again perhaps I'm reading the results incorrectly. Do you notice a large reduction in the defrag percentage when you run a defrag?
Cheers
Adam |
|
|
|
|
Mitchel Sellers
Site Moderator
Basic Member
Posts:328
|
| 18 Dec 2009 12:52 PM |
|
| Depends on the tables and the amount of traffic, you will not always get 0% fragmentation. |
|
-Mitchel Sellers, Microsoft C# MVP, MCITP
CEO
IowaComputerGurus Inc!
Visit my blog for helpful technical articles
For recommended products and services view the links to the right. |
|
|