Tag: database

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