SQL Server Log Jam

Yesterday we ran into a jam. This is the second time I've seen it in the midmarket. The lone SQL Server machine ran out of disk space and suddenly, mysteriously a couple key applications stopped working. Fortunately, I was using DTS to dump a text file into a new DB and got a wierd error. Aha.

Here's the solution from my business diary:

OK here's how to shrink MSSQL logs. Remember that you should check the database properties first. This shows where the logs go and if there are rollover files as well as the rule about their growth.

The interesting thing to remember is that the system knows the minimal size of the database log needed for recovery to the last backup (which I believe is the date on the .ldf file itself). So when truncate the log it will never make it smaller than it needs to be minimally. That is unless you have created a special kind of recovery profile for the database. This is in the Transact-SQL online help so I'm just putting it into English.

Here's the code I used at SCR for some fairly massive logs.

use master;
backup log projectserver with truncate_only;
use ProjectServer;
dbcc shrinkfile (ProjectServer_log, 800);
exec sp_helpdb projectserver;

use master;
backup log db_ControlManager with truncate_only;
use db_ControlManager;
dbcc shrinkfile (db_ControlManager_log, 5000);
exec sp_helpdb db_ControlManager;

Note that the 800 and 5000 are in MB as the target size for the logs. In our case we needed a more severe trim down to a couple hundred each.

http://blogoto.com/corey/archive/2004/08/03/166.aspx

BACKUP LOG WITH TRUNCATE_ONLY

Why is it that everytime one of my log files fills up I can never remember the syntax to truncate the log. By the way this only happens in my development environments when I try and do some massive update or delete of some sort and it only happens because I am very careful and I place size limits on data and log files so they don't auto grow and fill up a drive. This rarely happens but everytime it does I am scrambleing to remember the syntax to truncate the log so here it is:

BACKUP LOG [datbase_name] WITH TRUNCATE_ONLY

The reason this is an issue in my situation is because I need to clear out some test data, 13 million records in this case. Since there are so many foreign key constraints I can't just truncate the table unless I want to recreate the foreign key constraints. So to make things easier I just set the rowcount to 1 million execute the delete statement and let the log truncate itself on checkpoint and then run the delete statement again

SET ROWCOUNT 1000000
DELETE FROM table_name

I suppose I could also drop and recreate the table but then I would have to recreate the foreign key constraints, indexes and triggers.

AND

• Google Groups,
• Microsoft Support

Comments

[Christian Steven]

MS SQL FTW!

(my shortest comment ever!)