I am running a website with a SQL Server database attached.
My transaction logs are full and my hosting co. won't allocate more
disk space for me.

I need to delete my database transaction logs and asume I will need to
run an SQL script to do this.

Problem: I do not have MS Enterprise Manager of any database utility
on my website apart from MS Access. Where can I download a free SQL
tool that I can use to delete the transaction logs from my database.

Any help appreciated.

Thanks

Francois Terblanche
verismall.com

francois1@verismall.com wrote:
> I am running a website with a SQL Server database attached.
> My transaction logs are full and my hosting co. won't allocate more
> disk space for me.
>
> I need to delete my database transaction logs and asume I will need to
> run an SQL script to do this.
>
> Problem: I do not have MS Enterprise Manager of any database utility
> on my website apart from MS Access. Where can I download a free SQL
> tool that I can use to delete the transaction logs from my database.
>
> Any help appreciated.
>
> Thanks
>
> Francois Terblanche
> verismall.com

-- Apologies if this is a duplicate reply but I got a page cannot be
displayed error reply to the post message action

Is your database in SIMPLE or FULL backup mode? If SIMPLE then ask the
host provider to have their database support team run a backup and
perform a DBCC SHRINKFILE on the log files.

If as I would expect it is in FULL ask the host provider to run a
transaction log backup. Also inquire as to how frequently the
transaction logs are backed up?

If there is no space to run the backups ask the host provider how many
backups are being kept? If more than the last is still available on
disk you should be able to have the oldest backups deleted to make
space for the new backup.

HTH -- Mark D Powell --

(francois1@verismall.com) writes:
> I am running a website with a SQL Server database attached.
> My transaction logs are full and my hosting co. won't allocate more
> disk space for me.
>
> I need to delete my database transaction logs and asume I will need to
> run an SQL script to do this.
>
> Problem: I do not have MS Enterprise Manager of any database utility
> on my website apart from MS Access. Where can I download a free SQL
> tool that I can use to delete the transaction logs from my database.

On http://msdn.microsoft.com/vstudio/express/sql/download/ you can
download SQL Server Management Studio Express. It works for connecting
to SQL 2000 as well.

But before you go ahead, you first need to decide: which recovery mode
do you need? If the database goes belly-up, do you need to get the
database in a state as close to the crash as possible? Or are you content
with resoring from the latest backup?

And in either case, you need to enquire with your hosting company how
often they backup your database. If they cannot answer that question -
change provider.

If you need to up-to-the-point recovery, you must run with full
recovery and you must back up the transaction log regularly. Again,
ask your service provider about this.

In you don't need up-to-the-point recovery, use simple recovery. This
reduces the risk of running out of log space considerably. But not
entirely, as the log is never truncated past any open transaction.

You check the setting for your database with sp_helpdb.

Once you have connected, run this command:

ALTER DATBASE db SET RECOVERY SIMPLE
BACKUP LOG db WITH TRUNCATE_ONLY

(Both these commands are not necessary, but you did not say which version
of SQL Server you are using, so I like to cover both 2000 and 2005.)

If you need full recovery, then do.

ALTER DATABASE db SET RECOVERY FULL

and request that the hosting company takes a full backup of your database,
as by truncatingh the log, you broke the log chain.

It was suggested in another post that you should shrink the log file.
Don't do this. The web host will only give this space to someone else. :-)

....then again, if the support staff at your web host cannot help you
with this, I think it's time for a new web host.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx