Before leaving home i have checked the disk space in server. it was around 8 gb free. but the next morning only 10 mb is free. the whole night testing team worked on that server. when i still go through and check i found tempdb log file occupied 7.7 g.b . why it is so. can we shrink that tempdb database log file with out taking log backup. Also i want to know whether all the log files except recents(the day before) can be deleted or necessary to maintain.

-- Anonymous, April 12, 2003

Answers

Raja,

First of all, make sure that you have the "Truncate Log on Checkpoint" option set. If you are using SQL Server 2000, set the Recovery Model to Simple.

Then check these recommendations from Neil Pike of Protech Computing Ltd.:

Q. Why does my SQL Server log show that it's still full? - I have truncated it. (v1.0 17.10.1998)

A. The reason for this is that all the tools that interrogate log space - e.g. dbcc sqlperf, sp_spaceused and SQL EM all just look at the system catalog information in sysindexes - the dpages column. In SQL 6.5 and earlier this information is NOT kept up to date, so it is constantly wrong. The reason it is not kept updated is that it would cause a performance bottleneck.

The easiest way to correct the information is :-

dbcc checktable(syslogs) go checkpoint go

The information will then be correct until the next update/delete/insert transaction is issued.

If your log REALLY is full - i.e. you're getting a 1105 error on syslogs, then try a "dump transaction with no_log". If this still doesn't fix it, then one of the following is occurring.

1. You may have an open transaction. Check that with the following command.

use go dbcc opentran()

2. You may have un-replicated transactions. See Q184499 for info on this and how to clear it up.

See Q110139 and Q184499 for more information on syslogs filling up and how to resolve them.

Hope this helps,

Eric

-- Anonymous, April 14, 2003