Transaction Logs full--Need to Shrink ASAP 1

[ShawnF]

Help!

We've been having nothing but problems with our Transaction logs filling up to the point of completely filling up our 35 GB worth of hard drive. Our vendor that built the server in question (custom build W2k Server with SQL Server 2000) supposedly fixed the problem so that the log files won't grow to the point of filling up the hard drive, and I believe they did this by putting a limit size on the log file. The error message we were getting when the hard drive was full was that the transaction log files were full and to back up the database to free up some log space.

I found on the Microsoft site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp

which explains shrinking and truncating the log files, however it doesn't say where to go to issue the following commands:

"Shrinking the Transaction Log
The size of the log files are physically reduced when:

A DBCC SHRINKDATABASE statement is executed.

A DBCC SHRINKFILE statement referencing a log file is executed.

An autoshrink operation occurs."

I am by no means a SQL expert or even a novice, but I need to get this error message fixed ASAP because no one from our vendor is available right now, and no one here can edit any information in our customer database. I tried issueing the DBCC SHRINKFILE command from a DOS prompt and from the RUN menu but it didn't work (obviously). Where do I go to get this done?

The exact error message we are getting right now on any Win9x workstation is:

"The log file for database 'our database' is full.Backup the transaction log for the database to free up some space."

Any help is appreciated!

 

[tlbroadbent]

Run DBCC SHRINKDATABASE and DBCC SHRINKFILE from query analyzer. See the following link for more info and a way to immediately shrink the log.

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ShawnF]

Thanks for the quick response! I had no clue where to initiate the commands. Everything seems to be working for now. I called the vendor who built this server and they worked on the problem today. I don't know what they changed though, because they came in through Terminal Services instead of coming onsite.

I'll hopefully find out soon what was wrong and what was changed.

 

[grahamm]

1) Start query analyser. You should find it under:
Start-> Programs -> MS SQL Server 7.0 -> Query Analyzer

2) Connect to your SQL Server,

3) If your database is called foo type:-

dbcc shrinkdatabase (foo, notruncate)

However if you need SQL Server to release the space back to the file system (instead of just to SQL Server Databases) you will need to type:

dbcc shrinkdatabase (foo, truncate)

However if you haven't been backing up your database or log file, then the entire log file may be in use and I suspect the shrink won't help much. If you can, perform a back up first. See "Truncating the Transaction Log" in books online - i.e.

Start-> Programs -> MS SQL Server 7.0 ->books online

 

[miked123]

Could you expand on what caused the initial problem and the resolution? Was something setup incorrectly when the DB was created?

I'm fairly new to SQL server.
Thanks
MikeD