At work we have had issues with a SQL Server 2000 log file increasing its size and causing an error when trying to interact with the database:
[Microsoft][ODBC SQLServer Driver][SQL Server] The log file for database ‘db’ if full. Backup the transaction log for the database to free up some log space.
We had not had this error before so we ran some scripts to help work out what was going on.
The first thing we did was run DBCC SQLPERF(LOGSPACE). This gives you basic physical file size information. The example below puts the results into a table to query further.
CREATE TABLE #LogFileMonitor
INSERT INTO #LogFileMonitor
EXEC ('DBCC SQLPERF(LOGSPACE)')
SELECT * FROM #LogFileMonitor ORDER BY [LogSpaceUsed(%)] DESC
DROP TABLE #LogFileMonitor
Running this confirmed the log file in question was up to 100% capacity. To shrink the log file, you need to execute these commands:
BACKUP LOG db with truncate_only
DBCC SHRINKFILE (db_Log,2)
Once the log file was shrunk down, we explored the issues of why the log file was growing to the extent that it was causing a problem, as up until this point everything was ok. The only recent change to the database was a trigger, so I have dropped this. I will then use DBCC SQLPERF(LOGSPACE) to monitor the database as I execute SQL from the trigger to see what is causing the issue.
Some more useful commands:
-- ANLAYZE LOG FILES
WHERE name = 'db_Log'
-- CHECK RECOVERY MODEL FOR Database- simple, full, bulk logged