95 gig Transaction log!

Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.

The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.

We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.

The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.

We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.

See this ...http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653804&SiteID=1 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653804&SiteID=1)|||This still doesn't help me much. What I am looking for in instructions on HOW to run the commands to shrink the log file. Do I run it from command line? and if so do I just type in "DBCC SHRINKFILE filename" ?|||First make sure there are no open transactions:

select * from master.dbo.sysprocesses where open_tran > 0

then run:
dbcc sqlperf(logspace)

Use the name retreived above and run dbcc shrinkfile
dbcc shrinkfile([database_Log])

exec sp_spaceused

if the size does not go down, you can always dump the transaction log
dump tran [databasename] with no_log

Please do research before executing the last line of code to fully understand what you are about to do.

Good luck,
Hope this helped|||You should run it in query analyzer, assuming you are using sql server 2000|||I don't have much to add beyond what Reghardt has already said, but I will mention that properly scheduled backups of your transaction log should handle keeping your filesize down quite nicely.

My guess is that there is no transaction log backup scheduled, or they are scheduled, but have been failing for a while now & havn't been monitored.

Here is a great guide that should help you with this.
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1166473,00.html

Shrinking your log-file will solve your problem now, but it will just happen all over again down the road if you don't set up proper backups.

In addition, it is a very good idea to put a threshold on all of your transaction logs. That way, if a transaction log starts growing too big, it will only affect the database it is assigned to, instead of filling up your entire server.|||This worked perfectly, I was able to get it down to 4.6 gigs. Appreciate all the help.