Этот баннер — требование Роскомнадзора для исполнения 152 ФЗ.
«На сайте осуществляется обработка файлов cookie, необходимых для работы сайта, а также для анализа использования сайта и улучшения предоставляемых сервисов с использованием метрической программы Яндекс.Метрика. Продолжая использовать сайт, вы даёте согласие с использованием данных технологий».
Политика конфиденциальности
|
|
|
Shrink журнала транзакций
|
|||
|---|---|---|---|
|
#18+
Выполняю Shrink базы данных ,а у журнала транзакций зарезервированный объём не изменяется. Как сократить выделенный объём ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 19.11.2001, 13:28 |
|
||
|
Shrink журнала транзакций
|
|||
|---|---|---|---|
|
#18+
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 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 19.11.2001, 15:03 |
|
||
|
|

start [/forum/topic.php?fid=46&msg=32017264&tid=1824941]: |
0ms |
get settings: |
10ms |
get forum list: |
19ms |
check forum access: |
4ms |
check topic access: |
4ms |
track hit: |
63ms |
get topic data: |
14ms |
get forum data: |
3ms |
get page messages: |
52ms |
get tp. blocked users: |
2ms |
| others: | 233ms |
| total: | 404ms |

| 0 / 0 |
