Disconnect All Ussers But Me

I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it.

Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work.

Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from?

There is no command like that. But you could use ALTER DATABASE to set the database in single user or restricted user mode. This may or may not work depending on the permissions of the login used by the SSIS package and how often the clients reconnect etc. Check out the ALTER DATABASE topic and see.

Btw, why are you recreating the log file periodically? What is the purpose?

|||

just to add what Umachander has already told.. read about Alter Database and Singler_User in BOL first

ALTER DATABASE SomeDatabase SET SiNGLE_USER WITH ROLLBACK IMMEDIATE;

and ofcourse answer the abovementioned questions also... very risky and unethical method of truncating and shrinking log.. not atall recommended and you should not do this. there are another /professional approach to control the growth and size of log file.

Refer this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1272445&SiteID=1

Madhu

|||Thank you, I'll check those links out.

As for the reason, right now I inherited a database that is running some heavy SSIS packages, every time these run they do a lot of copying and deleting, in the end they inflate the log files drastically. However we don't really need the log files at all, we have all the data ready to be loaded in backup tapes should anything drastic happen.

I bet the SSIS packages can use a lot of optimization but for the time being I'm not in control of these and was instructed to take care of the logs ASAP. I already did this process manually and it worked so I figured it may be an OK temporary solution to run weekly until I got a hold of what is truly going on in this database and how to optimize it.

Heck, for the purposes of this project, if it was possible to disable logging as a permanent option, I'd do it.
|||If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables.

The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what you are trying to do anyway.