maandag 17 november 2008

beheerdb

USE [beheerdb]
GO
/****** Object: StoredProcedure [dbo].[beheerdb_dbc_chk_dbs_all_p] Script Date: 11/17/2008 10:28:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[beheerdb_dbc_chk_dbs_all_p]
AS
SET NOCOUNT ON

DECLARE @dbs_nam AS VARCHAR(50)
DECLARE @opd AS VARCHAR(1024)

DECLARE dbc_dbs_cur CURSOR FOR
SELECT [name]
FROM [master].[dbo].[sysdatabases]
WHERE [dbid] <>2

OPEN dbc_dbs_cur

FETCH NEXT FROM dbc_dbs_cur
INTO @dbs_nam

WHILE @@FETCH_STATUS = 0
BEGIN
SET @opd = 'dbcc checkdb ('+ @dbs_nam + ')'
PRINT 'Database: ' + @dbs_nam
EXEC (@opd)
FETCH NEXT
FROM dbc_dbs_cur
INTO @dbs_nam
END

CLOSE dbc_dbs_cur

DEALLOCATE dbc_dbs_cur

=====================================
Schedule a job to begin daily at 2:00
=====================================
USE [beheerdb]
GO
/****** Object: StoredProcedure [dbo].[beheerdb_dbc_her_ind_dbs_all_p] Script Date: 11/17/2008 10:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[beheerdb_dbc_her_ind_dbs_all_p]
AS
DECLARE @SQL Varchar(8000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + [name] +
'..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''?'''')''' + Char(13)
FROM [master].[dbo].[sysdatabases]
WHERE [dbid] > 4
PRINT @SQL
EXEC (@SQL)
=====================================
Schedule a job to begin daily at 2:30
=====================================

USE [beheerdb]
GO
/****** Object: StoredProcedure [dbo].[beheerdb_dbc_upd_sts_dbs_all_p] Script Date: 11/17/2008 10:15:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[beheerdb_dbc_upd_sts_dbs_all_p]
AS
SET NOCOUNT ON

DECLARE @dbs_nam AS varchar(64)
DECLARE @opd AS varchar(1024)
DECLARE dbc_dbs_cur CURSOR FOR
SELECT [name]
FROM [master].[dbo].[sysdatabases]
WHERE [dbid] > 4

OPEN dbc_dbs_cur

FETCH NEXT
FROM dbc_dbs_cur
INTO @dbs_nam

WHILE @@FETCH_STATUS = 0
BEGIN
SET @opd = 'Use ' + @dbs_nam + '
exec sp_updatestats'
EXEC (@opd)
FETCH NEXT
FROM dbc_dbs_cur
INTO @dbs_nam
END

CLOSE dbc_dbs_cur

DEALLOCATE dbc_dbs_cur

======================================
Schedule a job to begin daily at 5:45
======================================

USE [beheerdb]
GO
/****** Object: StoredProcedure [dbo].[beheerdb_dbc_upd_usg_dbs_all_p] Script Date: 11/17/2008 10:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[beheerdb_dbc_upd_usg_dbs_all_p]
AS
SET NOCOUNT ON

DECLARE @dbs_nam AS VARCHAR(50)
DECLARE @opd AS VARCHAR(1024)
DECLARE dbc_dbs_cur CURSOR FOR
SELECT [name]
FROM [master].[dbo].[sysdatabases]
WHERE [dbid] > 4

OPEN dbc_dbs_cur

FETCH NEXT
FROM dbc_dbs_cur
INTO @dbs_nam

WHILE @@FETCH_STATUS = 0
BEGIN
SET @opd = 'dbcc updateusage ('+ @dbs_nam + ')'
EXEC (@opd)
FETCH NEXT
FROM dbc_dbs_cur
INTO @dbs_nam
END

CLOSE dbc_dbs_cur

DEALLOCATE dbc_dbs_cur
======================================
Schedule a job to begin daily at 5:30
======================================

Geen opmerkingen:

Een reactie posten