Mar 5, 2008

unexpected log grow in Simple Recovery Mode

Log file was growing unexpectedly in Simple Recovery Mode during huge data purging process.
It was strange. My SQL Server is 2000 SP4.

Solution:
1. Check Trans Log file
The log was keeping growing.

2. Check Active Transaction
USE
[yourDB]
GO
DBCC OPENTRAN
GO

3.
Manually reset DB to Simple Recovery Mode
SP_DBOPTION [yourDB]
SP_DBOPTION [yourDB], 'trunc. log on chkpt.', 'true'
SP_DBOPTION [yourDB]

4. Shrink Trans log to the target size.
USE [yourDB]
GO
DBCC SHRINKFILE ('yourDBLog' , TargetSize)
GO

5. Monitor Trans Log growth.
Inactive transaction log entries should be cleared out at every checkpoint.

Should leave the log to a certain size, does NOT make sense to shrink it again unless an unusual over-volume transactions happen.

Mar 4, 2008

Harden Oracle and SQL Server - excelleent documents

http://www.cisecurity.org/

http://csrc.nist.gov/checklists/repository/category.html

Fix: "Difference of two datetime columns caused overflow at runtime" in Performance Dashboard.

Error: Difference of two datetime columns caused overflow at runtime
Solution: to modify msdb.MS_PerfDashboard.usp_Main_GetSessionInfo

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to:

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,