Analyzing SQL Server Log Files

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

[DatabaseName] varchar(100)
,[LogSize(MB)] real
,[LogSpaceUsed(%)] real
,[Status] int

INSERT INTO #LogFileMonitor

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:

Use db
BACKUP LOG db with truncate_only

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:


use db

FROM dbo.sysfiles
WHERE name = 'db_Log'

-- CHECK RECOVERY MODEL FOR Database- simple, full, bulk logged

select databasepropertyex('db','Recovery')


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s