Use master
go to
Sp_dboption' Your database name',' Single user',' true'
go to
DBCC checks the database ('Your database name', Repair _ Rebuild)
go to
Use your database name.
go to
Exec sp_msforeachtable 'DBCC checklist ('? , repair _ reconstruction)'
' exec sp _ msforeachtable ' DBCC DBREINDEX('？' ')'
go to
Sp_dboption' Your database name',' Single user',' false'
go to
-Then use it to compress the database log file.
Set NOCOUNT ON
Declare @LogicalFileName sysname,
@MaxMinutes INT，
@NewSize INT
- Select * From Sysfiles
Use yeserp-the name of the database to be operated on.
Select @ logicalfilename =' yeserp _ log', -log file name.
@MaxMinutes = 10,-Time limit for line breaks.
@ new size = 20- the size of the log file to be set (m)
-Setup/Initialization
DECLARE @OriginalSize int
SELECT @OriginalSize = size
From the system file
Where name = @LogicalFileName
Select' original size is'+db_name()+'and log is'+
CONVERT(VARCHAR(30), @OriginalSize)+'8K page or'+
CONVERT(VARCHAR(30)，(@ original size * 8/ 1024))+' MB '
From the system file
Where name = @LogicalFileName
Create table DummyTrans
(DummyColumn char (8000) is not empty)
Declare @Counter INT,
@StartTime date and time,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE()，
@ TruncLog = ' BACKUP LOG '+db _ name()+' WITH TRUNCATE _ ONLY '
DBCC Psychological File (@ Logical File Name, @ News Size)
EXEC (@TruncLog)
-Wrap logs if necessary.
WHILE @ MaxMinutes & gtDATEDIFF (mi, @StartTime, GETDATE())-There is no time.
/*expired*/ and @OriginalSize = (select the size from sysfiles, where name =@LogicalFileName) and (@ originalsize * 8/1024) > @NewSize
Start-external loop.
SELECT @Counter = 0
WHILE((@ Counter & lt； @OriginalSize/16) and (@ Counter & lt50000))
Start updating
Insert DummyTrans value ("Fill Log")
Delete DummyTrans
SELECT @ Counter = @ Counter+ 1
end
EXEC (@TruncLog)
end
SELECT' final size is'+db_name()+'log is'+
CONVERT(VARCHAR(30), size)+'8K page or'+
CONVERT(VARCHAR(30)，(size*8/ 1024)) + ' MB '
From the system file
Where name = @LogicalFileName
Drop table DummyTrans
Set NOCOUNT off.
-The second step is to compress the main data file.
Enterprise manager->; All tasks-> Compress database->; Compressed file-> File-> Compress to final size-> Just enter the minimum value.