Log Files!?!?

Hi,

I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.

Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.

How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?

Thanks,

Skip.I'm not aware of a db that is operating normally without a log.

first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.

But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.

You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.

When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!

Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.

Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?

Thanks again,

Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.

Log file size

I find that the following commands successfully decrease the size of the log
file:
- Backup Log MyDB With Truncate_Only
- DBCC SHRINKDATABASE (MyDB)
However the following commands (exec one after one) can't decrease the size
of the log file:
- DBCC SHRINKDATABASE (MyDb, TruncateOnly)
- DBCC SHRINKDATABASE (MyDB)
Can anybody explain why?
Thanks,
KMYou can use DBCC SHRINKFILE to shrink a log file. See the Books Online for
usage information.
Hope this helps.
Dan Guzman
SQL Server MVP
"krygim" <krygim@.hotmail.com> wrote in message
news:edS35FvIGHA.3056@.TK2MSFTNGP09.phx.gbl...
>I find that the following commands successfully decrease the size of the
>log
> file:
> - Backup Log MyDB With Truncate_Only
> - DBCC SHRINKDATABASE (MyDB)
> However the following commands (exec one after one) can't decrease the
> size
> of the log file:
> - DBCC SHRINKDATABASE (MyDb, TruncateOnly)
> - DBCC SHRINKDATABASE (MyDB)
> Can anybody explain why?
> Thanks,
> KM
>|||the TLog is made up internally of virtual log files. truncating the
log frees up the virtual files so shrinkdatabase/shrinkfile can take
back the space. if you run shrinkdatabase/shrinkfile without first
freeing up the virtual files, the space those virtual files take up
cannot be reallocated to the OS.
BOL has a GREAT explanation at
http://msdn.microsoft.com/library/d...r />
_1uzr.aspsql

Log all commands executed in QA

Hi All,

Is there way that commands executed in the query analyzer get logged
automatically?

TIA

JorizWhat do you want to do? Give more details

Madhivanan|||You can capture any SQL statements using SQL Profiler if that helps.

--
David Portas
SQL Server MVP
--|||(google@.joriz.is-a-geek.net) writes:
> Is there way that commands executed in the query analyzer get logged
> automatically?

In Control Panel, ODBC Sources under the Tracing tab, you can turn on
ODBC tracing. However, this level is on lower level on commands and
I believe it's mainly intended to debug ODBC drivers. But I believe
that you should be able to find the commands there.

However, I doubt that you want to use this. I turned it on to test
before I wrote this reply. And Query Analyzer become just amazingly
slow! A simple query that normally would return instantly, ran for
more than five seconds!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp