473,699 Members | 2,251 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 3234

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
3879
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 is constant. I haven't configured my database to do auto-shrink so Im really confused why it hasn't...
3
7573
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
2078
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 had used different restore options to test out the transaction that was being applied. And I couldn't...
3
1621
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 well on a frequent basis to ensure recovery to POF or point in time. I had assumed that they work...
0
1657
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 something strange. Yesterday the last transaction log was made at 19 and afterwards applied to the standby...
9
3473
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 that. Any other method? Thanks -Eugene
0
1166
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" our version of the live database by just applying the transaction logs, and not have to begin with...
1
2490
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 automatically every so often. Is that true?
4
18232
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
9184
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8930
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7767
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6538
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4382
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2013
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.