February 28, 2006 at 9:58 am
Hello,
I am an experienced SQL programmer, but a novice (at best) SQL administrator. Our Windows 2000 Small Business Server has SQL 2000 running on it. Inside of the Small Business Server Administration Console there are Health Monitor Alerts. I have two Critical alerts stating that the SQL Databases have a "Percent Log Used > 90". In the same window, there is also red "x" down the line of the tree for the Health Monitor. The tree follows this path:
Health Monitor | All Monitored Computers | Health Monitor Sample Monitors | SQL Server 2000 | Performance Counters | SQL Databases.
Is this a real message or just a "Sample" as the path shows? If this is a critical issue, how do I resolve this?
Any help is greatly appreciated with this issue.
Thanks!
February 28, 2006 at 4:20 pm
What is on the Transaction Log tab of the database properties? Is "Automatically Grow File" checked or unchecked? If it is checked then the log will grow automatically if you have space on the disk. Then it is not an issue. If you restricted log growth or you don't have space on the disk with the transaction log then it will be an issue.
What is the Recovery Model of the database? It is on the Options tab of Database Properties. If it is Simple then log mostly will be truncated on checkpoint unless a long-running transactions are running.
Regards,Yelena Varsha
March 1, 2006 at 9:34 am
Yelena,
Thanks for your response!
What is on the Transaction Log tab of the database properties? Is "Automatically Grow File" checked or unchecked? If it is checked then the log will grow automatically if you have space on the disk. Then it is not an issue. If you restricted log growth or you don't have space on the disk with the transaction log then it will be an issue.
ANSWER:
Automatically grow file is checked
File Growth - By Percent (25%)
Maximum File Size - Unrestricted file growth
What is the Recovery Model of the database? It is on the Options tab of Database Properties. If it is Simple then log mostly will be truncated on checkpoint unless a long-running transactions are running.
ANSWER:
Recover Model is Simple.
Server hard drive
68.2 GB Total
30.9 GB Used
37.3 GB Free
This server is the Domain Controller and it houses SQL Server and our most important and widely used application.
With the above listed hard drive space, do I need to concern myself with limiting the space of log files?
If so, what steps do I need to put in place to limit log file growth?
Do I currently need to run any SQL commands to shrink/delete all or part of the log files?
Thank you so much for your help.
Brent
March 1, 2006 at 9:46 am
Brent,
If I got it correctly, Recovery Model is Simple which means that log is truncated on checkpoint, you have a lot if disk space and automatically grow file is checked.
I would also restrict growth to appropriate number of MB on the same Transtaction Log tab of Database properties making sure log would not grow more then 80% of your disk space or maybe less, considering it is a domain controller.
Read about log truncation. Truncate on Checkpoint will clear inactive transactions from the log, but will not shrink the physical file. You have to know what is going on with your database: if there are long-running transactions updating /inserting lots of rows than log will be filled again and will grow after it fills up the allocated space. If you keep shrinking it then it will grow again in portions and your transactions will run slower. But if you know that the database activity is limited to ocasional inserts then maybe it is a good idea to shrink log once in a while.
Regards,Yelena Varsha
September 6, 2006 at 1:55 pm
Hello Yelena (or anyone else)...
I don't believe I have long running transactions. We enter new records (invoices, item transfers, etc.) throughout the normal working day. We do not have any users during the night. We do run a backup which takes approximately 6.5 hours and begins at 11 PM. I would like to shrink my transaction log files. I believe it is the cause for the error/warning "Percent Log Used > 90". What are the exact steps and commands needed to do this? I don't think this has ever been run against any of our databases. FYI: Not sure it it makes a difference, but Autoshrink is not checked for any of our databases.
Thanks in advance for your help.
Brent
September 6, 2006 at 2:14 pm
Run DBCC OpenTran on the database that has a large log file. You should get a result stating NO Open Transaction:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If thats the case the Add DBCC SHRINKDATABSE(DBName,25) immediately after the Full Nightly backup. This should shrink the log file.
Hope this helps
Thanks
Sreejith
September 6, 2006 at 3:18 pm
Sreejith,
Thanks for your response.
How do I determine which database(s) have large log files?
Should I run DBCC SQLPERF (LOGSPACE) and use your command on the Databases with "Log Space Used (%)" > 90?
Can I assume the Databases with "Log Space Used (%)" > 90 are the cause for the message in the Small Business Admin Console?
I ran the DBCC OPENTRAN on the databases with > 90 and I received the message in your reply. Then, should I use the command DBCC SHRINKDATABSE(DBName,25)? What is the 25? Does this actually shrink the size or do I have to also truncate the log files?
Thanks again.
Brent
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply