MSDE Admin Question
-
I have a database running on the MSDE version of SQL Server. I went to check the db maintenance plans and there is NONE! If I want to clean up the indexes on this database do I have to code it manually? Please advise.
Thanks in advance!
Mike
-
What do you mean by 'clean up the indexes'?
Are you looking for UPDATE/DROP STATISTICS...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Like in the DB Maintenance Plans. Check integrity and rebuild indexes. That type of stuff.
Mike
-
maintaining databases in BOL might be a good starting point
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can create Maintenance Plans for MSDE instances using Enterprise Manager, providing you have done some groundwork.
If you have MSDE 2.0 (SQL2K) installed, it helps a lot to be on SP3a to get this functionality to work. Even with SP3a there is a vital registry key that gets created with full SQL2K that is not set up with MSDE. You need to create the key HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory and set it to your ...\MSSQL\Backup directory.
If you want to use EM remotely to your MSDE box, then you have to enable a server network protocol on MSDE, such as TCP/IP, and grant your admin account System Administrator rights in MSDE.
All information provided is a personal opinion which may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
under your command prompet write (osql)
then you enter to your database
write
DBCC DBREINDEX
go
also search on microsoft.com for any other articles on MSDE and how to use Osql
by the way you can make maintainance plain on MSDE from any other MS SQL server EM on any other machine
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
under your command prompet write (osql)
then you enter to your database
write
DBCC DBREINDEX
go
also search on microsoft.com for any other articles on MSDE and how to use Osql
by the way you can make maintainance plain on MSDE from any other MS SQL server EM on any other machine
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
With a naked MSDE version 8.0 (also with 7.0) installation, it always worked fine with me, just to declare a stupid maintenance plan first, then refer the jobs to it:
/* ------------- MyDatabase MAINTENANCE PLAN --------------- */
DECLARE @PlanID nchar(36)
SELECT @PlanID ='CFC96CF4-9261-4646-88AA-8DF70020AABD'
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysdbmaintplans WHERE plan_id = @PlanID)
BEGIN
INSERT msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
VALUES (@PlanID, N'MyDatabase Maintenance', 1000, N'', 0)
END
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = N'MyDatabase')
BEGIN
INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, N'MyDatabase')
END
GO
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Optimizations Job for DB Maintenance Plan ''MyDatabase DB Maintenance Plan''')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Optimizations Job for DB Maintenance Plan ''MyDatabase DB Maintenance Plan'''' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Optimizations Job for DB Maintenance Plan ''MyDatabase DB Maintenance Plan'''
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Optimizations Job for DB Maintenance Plan ''MyDatabase DB Maintenance Plan''', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID CFC96CF4-9261-4646-88AA-8DF70020AABD -Rpt "C:\MSSQL\LOG\MyDatabase DB Optimization Job.txt" -DelTxtRpt 2MONTHS -WriteHistory -RebldIdx 10 ''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20020220, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply