March 3, 2008 at 9:48 am
HI
i just receive an email error msg from sql server by saying this..
The SQL Server performance counter 'Percent Log Used' (instance 'DATABASE') of object 'SQLServer:Databases' is now above the threshold of 75.00 (the current value is 94.00).
Can you tell me how can i get ride from this msg
thanks and looking forward.
-MALIK
March 3, 2008 at 12:05 pm
MALIK (3/3/2008)
HIi just receive an email error msg from sql server by saying this..
The SQL Server performance counter 'Percent Log Used' (instance 'DATABASE') of object 'SQLServer:Databases' is now above the threshold of 75.00 (the current value is 94.00).
Can you tell me how can i get ride from this msg
thanks and looking forward.
-MALIK
Sounds like your database is in full recovery model and you do not have any scheduled log backups. If your database is in full recovery model - you need to schedule frequent log backups (every hour would be a good start).
Once you have this setup, you can then shrink the transaction log file (this one time only) to reduce the size of the transaction log file to the appropriate size. The appropriate size is large enough to handle the number of transactions that occur during the time between each transaction log backup. If that size is too large, schedule more frequent log backups.
Jeff
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
March 4, 2008 at 3:44 am
Thank for reply.
no my database is not in recovery mode..and also i m taking regular schedule back..
March 4, 2008 at 8:11 am
You should be doing both full backups and transaction log backups if recovery is set to "Full" mode. Perhaps full nightly (or weekly, your decision) and transaction log backups on regular intervals to keep the file size down. Again, the schedules are up to you depending on your work load and company needs.
-- You can't be late until you show up.
March 4, 2008 at 2:24 pm
What does DBCC SQLPERF(LOGSPACE) tell you?
If you don't want to take regular log backups, you can always manually backup the log file and then shrink the transaction log. However, I'd suggest that if your log is filling up and growing, as suggested, you really need to look at your backup processes and evaluate whether you need to alter them.
March 4, 2008 at 11:18 pm
Hi Malik,
First of all you have to sedule log backup of that perticular database and then you have to shrink the log file to maintain you log space..
command :
1. backup log dbname with truncate_only
2. verufy the log space dbcc sqlperf(logspace)
3. shrink the log file using dbcc shrinkfile 'logfilename',value
Thanks
March 5, 2008 at 7:08 am
And, this has been stated numerous times in other postings, if you do a backup log dbname with truncate_only, do a full database backup immediately afterwards as you've now broken the restore logs chain for your previous full backup.
-- You can't be late until you show up.
October 6, 2008 at 9:54 am
Hi
I have similar problem, keep getting messages
The statement has been terminated.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'dbname' is full...
After running:
BACKUP LOG dbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbname_log, 1)
I'm still getting the same message.
My database is in 'simple' recovery mode and the backup is running every night...
October 6, 2008 at 9:59 am
Do you have any free space on the drive where your log file sits? Is the log file set to autogrow or is there a maximum size?
October 6, 2008 at 10:10 am
just.jelena (10/6/2008)
My database is in 'simple' recovery mode and the backup is running every night...
If you're in simple recovery, then running a truncate log will do almost nothing. The log is auto-truncated when a checkpoint runs.
Please run the following two queries and post the results
DBCC OPENTRAN
SELECT recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'dbname'
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
October 6, 2008 at 10:11 am
just.jelena (10/6/2008)
HiI have similar problem, keep getting messages
The statement has been terminated.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'dbname' is full...
After running:
BACKUP LOG dbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbname_log, 1)
I'm still getting the same message.
My database is in 'simple' recovery mode and the backup is running every night...
BACKUP LOG dbname WITH TRUNCATE_ONLY should be rarely run, unless you've hit the condition Clive stated "Do you have any free space on the drive where your log file sits?". This should not be something done with any frequency and should not be needed if the database is in simple mode. As Clive also stated, is the log set to autogrow or is there a maximum set? Even though you are in simple mode, depending on what is being executed, the log may need to grow until a checkpoint is reached but it should be minimal.
-- You can't be late until you show up.
October 6, 2008 at 10:27 am
Many thanks to everyone for the instant response!
It turned out that the problem was that I was running a large transaction that required more space than the maximum set for the log growth. Problem is solved by increasing this limit - as I found out, in a simple recovery mode that should be a good permanent solution since the log is not keeping the record of the transaction (no log backup etc needed).
Best rgds,
Jelena
February 19, 2016 at 11:01 am
Is it in Simple recovery mode or Full recovery mode or bulk logged recovery mode?
And ... do you know how to check this?
February 19, 2016 at 6:06 pm
jacqueline.trent (2/19/2016)
Is it in Simple recovery mode or Full recovery mode or bulk logged recovery mode?And ... do you know how to check this?
That and a whole lot more...
SELECT *
FROM sys.databases;
If you don't have "SQL Server Books Online" installed, search for it, download it, and install it. The, using the index, lookup "system tables [SQL Server]" and "system views [SQL Server]" for the beginning of a much better understanding of SQL Server.
And, yeah... it's free. No excuse not to. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2016 at 9:38 am
How do you know you are running a large transaction that required more log space?
Is there anyway that we can figure out how much log space it require for a transaction?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply