Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-01-24 : 13:48:41
|
| Ok, I must be blowing my mind here.I created a maintenance plan to run on Sundays at 10PM.This plan failed due to Tempdb running out of space.I rectified this and scheduled the maintenance plan to run the next day. This did not work either. The underlying jobs just did not kick off. Nothing in the event viewer or SQL Logs.The reason, the jobs that this plan created also need to be changed. Has anybody encountered this?Further, I have a SQL Express on my laptop, which I use for maintaining our db servers(SQL 2005 Standard Version). When I alter the portions of the Maintenance plans(e.g. I changed the selection of the databases for "Index rebuilding" part) using my work station connected to a Production/QA server,when I try to save the changes it gives me an error: "Failed GUID........and some 16 digit number most of which are all XXXXX". But if I change the schedule it does not give an error but it does not change it either.....I am really lost. I never had such problems on 7 or 2000.I am not sure if these are co-related. |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-01-26 : 17:09:13
|
| More issues.I created a maintenance plan to do the re-indexing of some heavily used databases where the fragmentation of indexes is over 90%. The job starts and dies somewhere. There is no trace as to where it dies, there is nothing in the event viewer, nothing in sql logs, nothing in job history or the maintenance plan log created in place of my choice. Anybody have ideas ?I found the issue with the Failed GUID. It seems there is a huge work around that issue and it is available all over the internet but this one has me totally foxed.Any help is appreciated. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-27 : 03:44:12
|
| Try manually reindexing the table, rather than using Maintenance Plan, and see if that gives you some more intelligible error message?Kristen |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-01-29 : 15:42:27
|
| Manual re-indexing seems to work without a problem. I can however NOT work off hours to do so and we CANNOT do this during office hours...hence I have to automate it. I know there are some weird problems with this. What I think I will do is to create these scripts for re-indexing and then club them all into one Job. and then schedule the job. I will keep you informed.Thanks for the help Kristen. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-01-30 : 13:56:08
|
quote:
Originally posted by pareshmotiwala
Ok, I must be blowing my mind here.I created a maintenance plan to run on Sundays at 10PM.This plan failed due to Tempdb running out of space.I rectified this and scheduled the maintenance plan to run the next day. This did not work either. The underlying jobs just did not kick off. Nothing in the event viewer or SQL Logs.The reason, the jobs that this plan created also need to be changed. Has anybody encountered this?Further, I have a SQL Express on my laptop, which I use for maintaining our db servers(SQL 2005 Standard Version). When I alter the portions of the Maintenance plans(e.g. I changed the selection of the databases for "Index rebuilding" part) using my work station connected to a Production/QA server,when I try to save the changes it gives me an error: "Failed GUID........and some 16 digit number most of which are all XXXXX". But if I change the schedule it does not give an error but it does not change it either.....I am really lost. I never had such problems on 7 or 2000.I am not sure if these are co-related.
Ok furhter issues:on a MS website, the following script finds the level of fragementation on the indexes:SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_idWHERE avg_fragmentation_in_percent >20AND NAME IS NOT NULLWhen I run this query, it gives me 20 indexes matching the where clause. I then run the Maintenance plan to rebuild and reorganize the indexes. At the end of the run of the plan, the results of the query don't change at all.the fun part is, when I look at hte index itself in the SSMS, it shows only 20% fragmentation...Anybody see this before? |
|
|
|
|
|
|
|
|
SQL 2005 Maintenance Plans