prevelik log fajl

Imam veliki problem sa SQL Serverom 2000.

Naime, log fajl stvarno ludacki raste. Da li je ikako moguce definisati sta treba a sta ne logovati ili da li postoji neki drugi nacin koji ce uticati na velicinu samih log fajlova?

Cinjenica je da se u toku dana odradi veliki broj operacija (na serveru je baza koja opsluzuje firmu od oko pedeset korisnika), ali opet sto je mnogo, mnogo je.

Pozdrav,

SM

---

U poslednjem postu teme http://www.elitesecurity.org/tema/151935 imas link koji ti moze pomoci.

Log fajl zapisuej sve transakcije (INSERT/UPDATE/DELETE) koje se dese u bazi. Kontrola rasta log fajla zavisi od strategiej za back up, kakko log-a tako i baze. Procitaj pomenuti link i bice ti jasnije.

Pozdrav,

Ne znam zasto bi kontrolisao velicinu log fajla tim sto bi birao sta se upisuje u log a sta ne, ali mozda ti ovo moze pomoci.

Napravi alert koji ce se aktivirati kada log poraste iznad neke vrednosti npr. 100Mb ili vec koliko ti mislis da je dovoljna velicina i podesi da kada se taj alert aktivira pokrece job koji ces takodje napraviti a koji ce imati sledece korake za izvrsavanja
(ako ti se baza zove npr prodaja)

--korak 1
backup log prodaja with truncate_only

--korak 2
DBCC shrinkdatabase(N'prodaja', TRUNCATEONLY )

--korak 3
backup log prodaja with truncate_only

--korak 4
DBCC shrinkdatabase(N'prodaja', TRUNCATEONLY )

p.s. nije greska koraci se dupliraju

na taj nacin ces automatski resiti problem velicine log fajl, a ako imas podesen sqlmail onda mozes podesiti da ukoliko dodje do greske prilikom izvrsavanja joba posalje email.

---

Pozdrav,

Log raste zato sto je za Recovery model baze podataka odabrana opcija "Full", ako ne radis Backup Log-a potrebno je da odaberes opciju "Simple".

A evo i finog skripta koji ce log file shrinkovati na optimalnu velicinu...


/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/

-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)

SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'

DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)

SET NOCOUNT ON

SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1

IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))

CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles

CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)

INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40

SET @file_max = @@ROWCOUNT

INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )

SET @last_row = @@rowcount

PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))

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

SET @unused1 = @unused -- save for later

-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))

-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10

-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution

SET @file = 0

WHILE @file < @file_max
BEGIN
SET @file = @file + 1

SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file

SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'

PRINT @command
EXEC (@command)
END

EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )

TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )

SET @last_row = @@ROWCOUNT

-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo

-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations

PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))

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


PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
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'


-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO

---