kalau saya begini om urutannya, yg pertama backup dulu.. kalau ada apa2 kan bisa restore lagi databasenya. kedua suspect, ke tiga reindex keempat shring. dari awal pake otomax pake ini aman
- Backup Database
DECLARE @name VARCHAR(50)
DECLARE @database VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'D:\' --- TUjuan Simpan File
SET @database = 'otomax'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' +
REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN (@database)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
- Suspect Database OtomaX
EXEC sp_resetstatus 'otomax';
ALTER DATABASE otomax SET EMERGENCY
DBCC checkdb('otomax')
ALTER DATABASE otomax SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
DBCC CheckDB ('otomax', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE otomax SET MULTI_USER
- Reindex DataBase OtomaX
USE otomax
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM
information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',95)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE
TableCursor
- Shrink DataBase OtomaX
USE OTOMAX
GO
DBCC SHRINKDATABASE(N'otomax' )
GO
DBCC SHRINKFILE (N'otomax' , 0, TRUNCATEONLY)
GO
DBCC SHRINKFILE (N'otomax_log' , 0,
TRUNCATEONLY)
GO
