| Author |
Topic |
|
tincupal
Starting Member
24 Posts |
 Posted - 2007-10-02 : 14:34:51
|
| We recently started using the SQL 2000 database maintenance plans. In doing this, we are not shutting down and restarting SQL. Therefore, the current SQL server log file is getting very big. Is there a switch in a maintenance plan to close and open a new SQL log file? If not, what is an easy way to close/open a new SQL log file?Thanks, Al |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-02 : 14:38:03
|
What are you referring to as the SQL Log file, do you mean the transaction log file or something else?
Future guru in the making. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 14:38:25
|
| What is your database recovery model set to? If FULL, are you performing regular transaction log backups? If so, how often? Do you know the differences between the database recovery models? What is the criticality of your data and how much data could you lose if something bad happened?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2007-10-02 : 14:44:17
|
| I'm referring to the main SQL log file that you see in the SQL Enterprise Manager, not the individual database transaction log (ldf) files. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 14:55:18
|
| Check out sp_cycle_errorlog in BOL. It cycles the SQL Server Error Log.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-02 : 14:56:41
|
Still not sure what you mean, are you referring to the "SQL Server Logs"? Are they stacking up, as in too many of them in the list? If that's what you are referring to, you can right click and specify the number of days to retain the logs.
Future guru in the making. |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2007-10-02 : 16:14:14
|
| It appears sp_cycle_errorlog is what I want. Here’s the definition from Query Analyzer:“Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.”Now, can I do this in a maintenance plan or only via SQL code?Thanks, Al |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2007-10-02 : 16:24:51
|
| Thanks everyone. I created a SQL Server Agent job to cycle errorlog on a daily schedule.Al |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 16:35:22
|
| I think everyone on SQL 2000 should do this at least once a week, if not daily. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 16:39:08
|
quote:
Originally posted by Kristen
I think everyone on SQL 2000 should do this at least once a week, if not daily.
Why?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 17:01:48
|
| I was meaning because the Log files grow and grow, assuming the server is not rebooted.Trying to open such large logfiles in Enterprise Manager, across a "modest" network link, takes ages.So better, IMHO, is to create new ones "relatively frequently" so that they can actually be opened, and checked, without fear of timeout etc. ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:07:14
|
| The problem with creating new ones relatively frequently is that you quickly lose the history. How often do you have to go back a quite a few days in the logs? For me, it seems pretty frequent. We reboot our servers monthly due to the monthly MS patches. We log quite a bit of info in the Error Log and yet do not need to cycle it. It's very rare when I issue the command and it's always manually.I don't use EM to open the largish files. Wordpad works quite nicely.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 17:12:03
|
Yup, agree with all that. Except that you aren't "Mrs Average User"  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:15:13
|
| Perhaps I'll make myself average by posting a new thread that starts with "My LDF file is 100GB, but my MDF is only 50MB. What can I do to fix this?" lolTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-02 : 17:33:19
|
quote:
Originally posted by tkizer
The problem with creating new ones relatively frequently is that you quickly lose the history. How often do you have to go back a quite a few days in the logs? For me, it seems pretty frequent. We reboot our servers monthly due to the monthly MS patches. We log quite a bit of info in the Error Log and yet do not need to cycle it. It's very rare when I issue the command and it's always manually.I don't use EM to open the largish files. Wordpad works quite nicely.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
You can update the # of archive logs to keep on the server. I have several very busy servers that we cycle the errorlog on a weekly basis and keep 26 archive logs to keep the last six months online... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:37:35
|
| I'd do that if I had a need to cycle the log. Wordpad works great for large log files, so I don't see the need to cycle it (except on rare occassions).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-02 : 17:46:30
|
| One server has 40+ databases in FULL recovery model with t-log backups every 15 minutes daily. 96x40 = lots of annoyying backup entries get written. I should mention that I have a daily report that greps the log for specific keywords and delivers it via email. It's rare that I actually consult the errorlog itself anyways...Btw, beware that wordpad uses an odd keyspace and _might_ corrupt the file. I always remove wordpad from servers for this particular reason...use notepad instead - less risk IMO. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:52:58
|
| We have a similar setup as you.I don't use Wordpad on the actual database server. I pull a copy of the intended file to another machine for review. I do this for large files only. For smallish files, I use EM/SSMS.Notepad is just as slow reading the file as Enterprise Manager.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-02 : 18:18:35
|
The best tool for large log files, as Kristen can attest to, is Visual Slickedit. Opens em up in a flash.
Future guru in the making. |
|
|
|
|
close & open new sql log file