July 8, 2008 at 7:16 am
Hi,
I'm supporting a set of legacy maintenance procedures that run on a daily basis. The environment is SQL 2005 sp2 upgraded recently from SQL 2000 sp3a. Each day the following steps are taken. The application services are stopped, the MSSQL Server service is bounced, backups are taken, the MSSQL Server service is bounced, an Integrity check is run and then the following Optimization is run:
DECLARE @plan_id NVARCHAR(100)
SELECT @plan_id=CAST(plan_id AS NVARCHAR(100))
FROM msdb.dbo.sysdbmaintplans
WHERE UPPER(plan_name)='DATABASE MAINTENANCE PLAN'
-- Create command variable.
DECLARE @command NVARCHAR(1000)
-- Create optimisation command.
SET @command = N'master.dbo.xp_sqlmaint N''-PlanID '+@plan_id+' -Rpt "D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\Database Optimisation.txt" -DelTxtRpt 3DAYS -WriteHistory -RebldIdx 5 -UpdOptiStats 50 '''
EXECUTE (@command)
END
The Backup and Integrity tasks (which are similar) run fine, but recently I've been getting the following at the end of the Optimization log:
[14] Database ProductDB: Updating Query Processor Statistics (sampling 50 percent of the data)...
NULL
NULL
** Execution Time: 0 hrs, 0 mins, 1 secs **
NULL
NULL
[SQL Native Client]Shared Memory Provider: The pipe has been ended.
[SQL Native Client]Communication link failure
The task is running while the application services are down, and ProductDB is the last database to be checked. Seemingly all the steps in the task are done after about half an hour, and then it hangs for approximately another hour before adding the disconnect.
Any thoughts would be greatly appreciated as I cannot see why it would disconnect at this point.
Cheers,
Dave Fournier
July 8, 2008 at 8:00 am
I would start by ignoring the disconnect issue for now.
You should set up a new maintenance process. It should not include rebooting the server or doing anything with the database offline. That may have been necessary in the SQL 6.5 or 7 days, but now it is just ruining your cache on a daily basis and messing up the dynamic management views.
Set up a more traditional maintenance plan using SSIS. It's time to re-think the process and configure it more like you are running SQL 2005.
July 8, 2008 at 9:15 am
Hi,
In an ideal situation, that's what I'd do. As you might be inclined to guess, this is pretty far from an ideal situation.
This isn't an in house database system. These are maintenance tasks deployed on a set of "sites". Each site has one or more applications which have their own databases, which reference several core databases. Globally there are more than 200 sites. I've got it on my radar to rewrite the maintenance for 2005, but in the meatime I have to support the existing structures.
Cheers,
Dave
July 8, 2008 at 9:22 am
Question: did you RUN DBCC UPDATEUSAGE and DBCC CHECKDB on the Databases after the upgrade.
Also do you perform a index fragmentation check and rebuild.
For your Current ISSUES: Try UPDATE STATISTICS on all the tables of the Database in Question.
then check back to see if the messges reappear.
Maninder
www.dbanation.com
July 8, 2008 at 9:41 am
Thank you for your response.
At this point, after continuing my investigations, it does appears that the command to Update Statistics has recently been added, and will now be removed.
Cheers for your help, and now I was hoping to get feedback in the logical follow up question:
If you had to deploy, a maintenance strategy for 200 plus sites, how would you do it? It's a small point, but this is an adjustable system that has a variable amount of databases accessed exclusively through application services. Downtime, for the application services, must be kept to a minimal. Bear in mind this will be deployed by desktop support staff, so must be fairly simple to deploy (i.e. an SQL script). The focus would be on reporting rather than live fixes, as the sites have very limited downtime which would need to be pre-scheduled.
Cheers,
Dave
July 8, 2008 at 9:47 am
this thing often happen or just happen one times?
if often happen,you could check your OS 's tasks,
maybe there are some conflicts there.
If not often happen then maybe network problem in the time.
July 8, 2008 at 9:57 am
is the Database set to AUTO_CLOSE or AUTO SHRINK.. remove these 2 Options.
Maninder
www.dbanation.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply