Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Krishna_Sridharan
Starting Member
3 Posts |
 Posted - 2008-09-29 : 07:19:57
|
| Hi,i have a hosted web application which is using sql server 2005 database. when i try to execute any query, i'm getting this error."The log file for database is full. Back up the transaction log for the database to free up some log space."i locked with this error. Could not execute any queries. What should i do to get the things done? Actually we use to update the data by running the scripts in query analyser by connecting the remote machine using SQL server 2005 by providing the server name with SQL authentication.How can i free up the space to proceed further? Pleazs its urgent. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 07:27:24
|
1) Take regular transaction log backups.2) Take regular transaction log backups again.To remedy this situation right now and thus breaking the backup chain, writeBACKUP {db name here} LOG WITH TRUNCATE_ONLY
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 07:32:14
|
See log space as the size of the suitcase you pack when hoing on vacation.When you have filled your log space you need bigger suitcase, right?You can then extend the log file on your disks, at the price of eventual fragmentation of log file.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Krishna_Sridharan
Starting Member
3 Posts |
Posted - 2008-09-29 : 08:38:22
|
I dont have rights to see the log space physically as the server is in different location. i connected the machine using SQL server 2005 management studio by providing the server name, User name and pwd given by the client. I can take backup of log files by running using your given query (BACKUP "DATABASE" Log....). Will this alone free up my log space? If i take backup of Log files using the given query, Where will it get stored? Please help i'm new to this task.quote:
Originally posted by Peso
See log space as the size of the suitcase you pack when hoing on vacation.When you have filled your log space you need bigger suitcase, right?You can then extend the log file on your disks, at the price of eventual fragmentation of log file.
E 12°55'05.63"N 56°04'39.26"
|
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2008-09-30 : 09:44:29
|
First, see here on how the different recovery models affect your data recoverability.Either your database is running using full/bulk-logged recovery model, or you have an old transaction open, or you have a large transaction that's running. See here on how to identify the last two scenarios.Having done that, you can perform one or more of the following:- change your database's recovery model to suit your disaster recovery/high availability needs- commit/rollback any old transactions as required for more space for your transaction log- request for more space to store your transaction log- set up jobs to back up your transaction log periodicallyIf you run a log backup with the NO_TRUNCATE option, the log is not backed up anywhere. You can back up the transaction log to a file if you need it for future recovery purposes, using the TO DISK option e.g.BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks_log.bak' Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Inspect - SQL Server query analysis made simpleSQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases. |
|
|
|
|
|
|
|
|
"The log file for database is full."