Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Shrink журнала транзакций / 3 сообщений из 3, страница 1 из 1
19.11.2001, 13:28
    #32017264
sasV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shrink журнала транзакций
Выполняю Shrink базы данных ,а у журнала транзакций зарезервированный объём не изменяется. Как сократить выделенный объём
...
Рейтинг: 0 / 0
19.11.2001, 15:03
    #32017270
olga
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shrink журнала транзакций
cut and paste this :
( this script usually works very well for Shrink transaction log )

use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on

declare @db sysname,
@last_row int,
@log_size decimal(15,2),
@unused1 decimal(15,2),
@unused decimal(15,2),
@shrinkable decimal(15,2),
@iteration int,
@file_max int,
@file int,
@fileid varchar(5)

select @db = db_name(),
@iteration = 0

create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )

create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @file_max = @@rowcount

if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount

select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo

select @unused1 = @unused -- save for later

select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )

while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations begin
select @iteration = @iteration + 1 -- this is just a precaution

exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end

exec( 'backup log [' + @db + '] ' + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount

select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo

select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10

else
select 'It took ' + convert( varchar, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB'

exec( 'drop table table_to_force_shrink_log' )
go



--------------------------------

a eto test :

use master
go
-- This creates a test database on drive C:. You may substitute a different path.
CREATE DATABASE [test] ON PRIMARY (
NAME = N'test_Data',
FILENAME = N'C:\MSSQL7\data\test_Data.MDF' ,
SIZE = 1,
FILEGROWTH = 10%)
LOG ON (NAME = N'test_Log',
FILENAME = N'C:\MSSQL7\data\test_Log.LDF' ,
SIZE = 1,
FILEGROWTH = 10%)
go
use test
go
dbcc loginfo
go

-- Create a test table and populate it with some data to show how the log grows:
create table test ( a char(8000) not null )
go
set rowcount 200
insert test select a.name from sysobjects a, sysobjects b
set rowcount 0
go
drop table test
go
dbcc loginfo
go

-- Backup the log to show that the Status of most virtual log files changes to 0:
backup log test with truncate_only
go
dbcc loginfo
go

-- Fruitless attempt to shrink the log:
dbcc shrinkfile ( test_Log )
go
dbcc loginfo
go
...
Рейтинг: 0 / 0
19.11.2001, 15:10
    #32017271
Александр Гладченко
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shrink журнала транзакций
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Shrink журнала транзакций / 3 сообщений из 3, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]