Tag Archives: windows

mssql database transaction log blown

hi there

sometimes you have to fix an mssql server where transaction log is full. due to that the database is more or less unusable

*) check for big tables

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

*) create backup of mssql db

BACKUP DATABASE yourdbname
TO DISK = 'e:\backup\db-bkup.sql'
GO

*) backup transaction log of mssql

BACKUP LOG yourdbname TO DISK = 'e:\backup\yourdb_log.trn' WITH INIT;

*) shrink transaction log

USE yourdbname;
GO
DBCC SHRINKFILE(yourdbname_log, 200);
GO
USE [master];
GO
ALTER DATABASE yourdbname 
  MODIFY FILE
  (NAME = yourdbname_log, SIZE = 200MB, FILEGROWTH = 10MB);
GO

*) disable/reduce transaction log (if you don’t need it)

ALTER DATABASE yourdbname SET RECOVERY SIMPLE;
USE yourdbname;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdbname_log, 200); -- unit is set in MBs
GO

*) select data from table

SELECT [a]
      ,[b]
      ,[c]
      ,[d]
      ,[e]
      ,[f]
 FROM [yourdbname].[dbo].[yourtablename] WHERE timestamp < DATEADD(MONTH, -36, GETDATE()) ORDER BY timestamp ASC

*) delete data from table older than 3 years

DELETE FROM [yourdbname].[dbo].[yourtablename] WHERE timestamp < DATEADD(MONTH, -36, GETDATE())

*) shrink databasefile (.mdf) to small size

ALTER DATABASE yourdbname SET RECOVERY SIMPLE;

mssql will produce immense IO and transaction logfile size

USE [yourdbname];
DBCC SHRINKFILE(yourdbname_dat, TargetSize);

*) limit ram usage of mssql server

properties of database-server -> memory -> maximum server memory

have fun