|
|
Posted by Erland Sommarskog on 10/02/03 11:55
(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|