Compacting a db via T-SQL

Hello dbForumers,

I was wondering if there is a way (MUST be) to instruct SQL server to compact a chosen database's files. I have a batch that runs every night who generate a huge amount to log lines that I get rid of with a backup log xxx with truncate_only, but still the logfile is several GB big afterwards with a lotsa empty space... I wanna get a clean small file everyday =)

Thank you!Its not ideal to shrink the Transaction log every day, rather define a set of size by testing the activity on the database.

During that batch overnight take before and after sizes for Tlog and set the higher level.

Also maintain regular backups of Tlogs which will reduce the size of logical file and helps to fillup the Tlog quickly. If RECOVERY MODEL Is set to SIMPLE then ensure full database backups are carried in regular intervals.|||try DBCC SHRINKFILE

USE UserDB
GO
DBCC SHRINKFILE (logical file name,Target_size)
GO

Target_size is how much free size is left over after you shrink
if you leave this off, you will get the default.

also
you may have to switch the VLogs internally before you can shrink to a size that you desire...
all of his should be done after a tlog backup.
look up "Shrinking the Transaction Log" AND DBCC Shrinkfile in [BOL]

Commit and Rollback on a batch

Hi!

I am using VB.NET 2005 and/or SQL Server 2005 studio manager.

I have a string of about 20 or 30 inserts updates and deletes. I want to process all or nothing. If there is an error that prevents a single transaction from completeing, i want to roll back the entire batch.

One solution i read is to test fot @.@.error after each statement. This is not desirable as i receive the batch as a single string already made. I would have to separate all the statements and insert the error testing myself.

I would prefere to simply execute the batch as an all or nothing batch.

Certainly this is a common request. But the only solutions i can find involve extensive re-working of the source batch of transactions. I may have up to 100 statements in my batch.

Any Ideas?

Thank you

Jerry Cicierega

using System.Transactions;

...

using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
// Do stuff with your cmd object here such as running the aforementioned batch statements
}
}
ts.Complete()
}

If an error occurs during the processing, the whole thing will be rolled back. Of course, fill in the stuff you need for the connection and command objects in the using statements.