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