473,554 Members | 2,919 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

transaction logs full

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

Aug 14 '06 #1
2 3227

fr*******@veris mall.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 --

Aug 14 '06 #2
(fr*******@veri small.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, es****@sommarsk og.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
Aug 14 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
3861
by: enigma | last post by:
Just wondering if you could help me on this one. I'm not sure if my transaction logs are behaving oddly or what. I've successfuly managed to shrink my transaction logs from 7GB down to 1MB and now I find it strange that the log file doesn't seem to increase its size. The timestamp of the logfile is updating as well. But the size of the file...
3
7561
by: Thiko | last post by:
Hi I take one nightly full database backup at 02:00 and backup the transaction log to one backup set every 15mins. The commands to do this are as follows and are set up to run as database jobs: -- Database Backup BACKUP DATABASE TO WITH NOINIT ,
0
2066
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the database on the backup server. Meanwhile, I leave the database ready to accept transaction log from the transaction log backup at noon daily. And I...
3
1610
by: TG | last post by:
Coming from a (mostly) Oracle shop, I am unclear how SS transaction logs work as far as up to point of failure recovery goes. I have the few MSSQL databases I look after in full recovery mode, and I set up maintenance plans to back these up online on a daily basis. In the research I did it was recommended backing up the transaction logs as...
0
1654
by: flobroed | last post by:
Hi, I've a question regarding the transaction-log backup on SQL-Server 2000. We have implemented a low cost replication. Every evening we make a full backup and beginning at 7 to 18 we make transaction-log backups which are restore (no recovery) to the "standby-server". The full backups are restored every evening. Today i noticed...
9
3464
by: Eugene F | last post by:
The database is currently in the circular logging mode (no log archiving). I want to track the database transaction log switch history in form of (switch-timestamp, active-log-sequence-number) to eventualy assess on the database logging volume during the database operations (OLTP). I wasn't able to find a relevant event monitor elemen for...
0
1160
by: DUG | last post by:
There is a server in a remote location. We receive a full .bak of this database to restore on our server, where we use the data for a seperate purpose. The .BAK file is almost 3GB - zipped! They also send us transaction logs 2x / day. We would like to use those, and not download the full .BAK every night. Is it possible (how?) to "update"...
1
2487
by: JA | last post by:
Hi, I know just about nothing about SQL Server. I am getting this error: The log file for database 'my_database' is full. Back up the transaction log for the database to free up some log space. I can't access the transaction logs to back them up. I am told that my ISP's tech support should have it set up to shrink those logs...
4
18218
by: Brian D | last post by:
In MS SQL 2005 when you do a Full Backup does it also backup and truncate the transaction logs or do I need to back the transaction logs up separately? Thanks. Brian
0
7600
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7802
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8042
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6145
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5436
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5155
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3560
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2020
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
841
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.