July 8, 2008 at 9:54 pm
Hi,
we are using ms sql server express 2005 and we are getting the error
The transaction log for database 'database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
please recommend
thnax
July 8, 2008 at 10:17 pm
ltoso (7/8/2008)
Hi,we are using ms sql server express 2005 and we are getting the error
The transaction log for database 'database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
please recommend
thnax
What is the recovery model for this database?
How often do you backup the transaction log (if full recovery model)?
How often do you backup the database?
And, what is the value of log_reuse_wait_desc column in sys.databases for this database?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2008 at 11:25 pm
Hi,
thanx for the fast reply, i am very new to sql server so can you help me further like
What is the recovery model for this database?
where can check this
How often do you backup the transaction log (if full recovery model)?
i don't backup the transaction log
How often do you backup the database?
i backup the db daily using a third party t-sql script daily
And, what is the value of log_reuse_wait_desc column in sys.databases for this database?
how can i check this, i browsed to db using sql server management studio and then went to the column log_reuse_wait_desc under views > system views >sys.databases
but when i clicked on it it didn't showed any value
i also want to tell you that this is a shared server with many other accounts but no other database is giving this problem
is there a way we can increase the transaction log size
will retarting sql server express help
please recommend
July 9, 2008 at 12:03 am
ltoso (7/8/2008)
Hi,thanx for the fast reply, i am very new to sql server so can you help me further like
What is the recovery model for this database?
where can check this
Open a query window and run
SELECT [name], recovery_model_desc, log_reuse_wait_desc FROM sys.databases
is there a way we can increase the transaction log size
will retarting sql server express help
Yes, you can increase the asize of the tran log, but I don't think that's what you want to do. Restarting SQL won't help.
My guess is that you are funning in full ecovery mode and have no tran log backups. If you don't backup the log in full recovery mode, the log will grow without bound, as old log records are never discarded.
Please check the recovery model and confirm.
How important is the data in this database? If you have a failure, is restoring to the last full backup acceptable, or do you need to be able torestre to the point of failure?
What are you using to backup the DB? Things like NTBackup won't take proper backups of a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 12:15 am
If your tranlog is full then you cant really do much, I would try to see if there are any open transactions you can do this by using the dbcc opentran if there is a long running process kill this
the back up the log with a no_log command
then use the db and then use dbcc shrinkfile and then the db name, 200 or so where 200 is the size of the file you want. this will shrink the log. Then you will need to back up that databases again in full mode as with no log might be bad for the future as it will not allow recovery.
Try this.
Terry
July 9, 2008 at 12:17 am
terry.jago (7/9/2008)
the back up the log with a no_log command
no_log is deprecated in SQL 2005 and not recommended for use. If log backups are not required, then just switch the DB to simple and leave it.
Shrinking the entire database (as opposed to just the large log file) will cause fragmentation and potentially reduce performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 12:25 am
Hi,
thanx for fast reply
i ran the the query given by you and it showed that the recovery_model_desc is full and log_reuse_wait_desc log_backup for all other databases it is set as simple and nothing
but how can a user that has access to only his databases set it, as i have told you this is a shared server with hundreds of dbs on it and dbs are created using helm.
so what do you recommend i should do to get rid of the error and start site working again we backup the db using the following third party script to backup daily
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = 'C:\Backuped_SQL_DB\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0
BEGIN
SET @Name = @DB + '( Daily BACKUP )'
SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
i don't know how much the data is important as the original owner of the website hasn't contacted us yet
please recommend
July 9, 2008 at 12:36 am
Set the DB to simple recovery mode, like all the others, then do a once-off shrink of the log (just the log) if it's larger than it's supposed to be. Use DBCC ShrinkFile
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 12:41 am
Hi,
thanx for the fast reply
can you tell me the commands to do it as i am very new to sql server .
one more thing i would like to know can the user himself who has access only to his database himself do it using sql express management studio.
in another post you have written that shrinking the size will effect performance
thanx
July 9, 2008 at 12:51 am
You can do it through management studio. Go to the database properties and to options. It's at the top of that screen.
You can shrink the log file there also. Right click the db, go to tasks, shrink, file and make sure you only shrink the log file. Shrinking any of the data files may reduce db performance.
If the user in question is db_owner, he can change the recovery mode via managment studio GUI, or query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 1:05 am
Hi,
thanx for fast reply
when we go to shrink file and choose log under shrink action it shows the following
release unused space
or
reorganize pages before releasing unused space
shrink file to 27mb(what should be mentioned here)
or
empty file my migrating the data to other files in the same filegroup
one more info i would like to give you it is showing current allocated space 27.44
the user is the owner of the db so that means he can do these options himself,
if the user wants to continue using full mode what will be the solution will we need to increase his allocation in the later stage
please recommend
July 9, 2008 at 1:17 am
ltoso (7/9/2008)
Hi,shrink file to 27mb(what should be mentioned here)
That's the option you want. To know how big to make it, you need to know how much space the log typically uses. If you don't know, maybe leave the log the current size and monitor its usage for a few days (using DBCC SQLPERF(LOGSPACE)) then once you know the size it needs to be (in simple recovery) you can shrink it to that size.
What size if the log file now?
if the user wants to continue using full mode what will be the solution will we need to increase his allocation in the later stage
Regular transaction log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 1:34 am
Hi,
That's the option you want. To know how big to make it, you need to know how much space the log typically uses. If you don't know, maybe leave the log the current size and monitor its usage for a few days (using DBCC SQLPERF(LOGSPACE)) then once you know the size it needs to be (in simple recovery) you can shrink it to that size.
but how can we monitor it if it is using the whole 27 MB right now we have to give a value to shrink its size what do you recommend what value should we give
where do we have to give the command using DBCC SQLPERF(LOGSPACE) to check log size is it the complete command
What size if the log file now?
27.44MB it is using all the space that is why we are getting the error transaction log is full
if the user wants to continue using full mode what will be the solution will we need to increase his allocation in the later stage
Regular transaction log backups.
what is the procedure to take transaction log backup, how is it different from backup that we take by right clinking the Db and choosing backup
that means if they take regular backups the transaction log gets cleared or they have to delete the transaction log manually if they have to delete the transaction log manually
please recommend
Thanx
July 9, 2008 at 1:39 am
Please edit your post to fix the scrolling. use [ quote ] rather than [ code ] (removing spaces)
If the log is only 27 MB, leave it alone. It's small enough. Have you set the Db into simple recovery mode?
As for transaction log backups - check Books Online for all the details.
Full backups do not clear the transaction log.
Do not try deleting the transaction log. It's a fast way to get a corrupt database.
Edit: Do you have anyone familiar with SQL Server there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 1:55 am
Please edit your post to fix the scrolling. use [ quote ] rather than [ code ] (removing spaces)
sorry i am new i have edited it to quote
If the log is only 27 MB, leave it alone. It's small enough. Have you set the Db into simple recovery mode?
do you mean to say i should shrink it to 27mb or something else the current size of transaction log shows as 27MB and which is also set as the largest size possible
will setting it to simple recovery mode remove the error of "transaction log full"
As for transaction log backups - check Books Online for all the details.
Full backups do not clear the transaction log.
Do not try deleting the transaction log. It's a fast way to get a corrupt database.
Edit: Do you have anyone familiar with SQL Server there?
Nope i don't have anyvody that is familiar with sql server, i am trying to get myself familiar should i get some book or something to get myself familiar.
please recommend.
Thanx
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply