Easy question about server log files

SQL Server 2000 running on Windows 2000

Is there a way to set up the SQL Server Log files so that it automatically creates a new one (or overwrites the old one) when the old one is full?

We keep having people shut out of our web site due to the log file being full. Since we have no DBA I volunteered to try to find out how we can avoid this faux pas in the future.

Thanks for your help!New idea for Microsoft !!!

If seriously may be it will be easier to backup transaction log more frequently (if full model) or increase maximal size of log because additional log files will only consume disk space until it is full.

It needs to find out why log is growing so fast - may be it is because of not closed transaction or something else.|||Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?|||Originally posted by Fly Girl
Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?

I am taking my words back...

Right click on SQL Server Log files/configure:

BOL:

Configure SQL Server Error Logs
Use this dialog box to view or specify the following options.

Options
Limiting the number of the error log files before they are recycled.

Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of Microsoft SQL Server is started. Typically, SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.

Maximum number of the error log files.

Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.|||Ok ... we need to get this clear ...

First of all .. I dont think it would be the SQL server error logs that are causing the problem.

Connect to the server ... using QA and run the following commands

exec xp_fixeddrives

and

sp_helpdb 'production_databasename'

Copy paste results and we will go further.|||Originally posted by snail
Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.

And you can create a scheduled to be run daily in SQL Server
Command to be executed in Job :-
dbcc errorlog|||OK, I think I've got this one ironed out now.

Thanks for the help!

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:
> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
Systems Programmer
use a unc path \\servername\shareName
Greg Jackson
PDX, Oregon
|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>
sql

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when
I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

Dynamic Sql limit

I am trying to set up an Sql Statement that will run at various times of the
day automatically. I have to use a Dynamic string and am getting an error:
Server: Msg 103, Level 15, State 7, Line 3
The identifier that starts with 'Select distinct
Rank=0,l.CompanyID,m.UserID,
FullName= Case When (ResumeConfidential=0 and HideNameS=1) or
(ResumeConfidential' is too long. Maximum length is 128.
My code is something like:
Declare @.Search VarChar(8000)
Select @.Search = "Select distinct Rank=0,l.CompanyID,m.UserID,
...
Execute(@.Search)
GO
How do I get around this limitation?
My string could be 1000 characters long or more.
Thanks,
TomUse ' not " to delimit strings.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OetZFxMMGHA.3272@.tk2msftngp13.phx.gbl...
>I am trying to set up an Sql Statement that will run at various times of
>the day automatically. I have to use a Dynamic string and am getting an
>error:
> Server: Msg 103, Level 15, State 7, Line 3
> The identifier that starts with 'Select distinct
> Rank=0,l.CompanyID,m.UserID,
> FullName= Case When (ResumeConfidential=0 and HideNameS=1) or
> (ResumeConfidential' is too long. Maximum length is 128.
> My code is something like:
> Declare @.Search VarChar(8000)
> Select @.Search = "Select distinct Rank=0,l.CompanyID,m.UserID,
> ...
> Execute(@.Search)
> GO
> How do I get around this limitation?
> My string could be 1000 characters long or more.
> Thanks,
> Tom
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u$FUL2MMGHA.2316@.TK2MSFTNGP11.phx.gbl...
> Use ' not " to delimit strings.
How do I do that if my string has single quotes in it?
For example:
Select @.Search = 'Select distinct Rank=0,l.CompanyID,m.UserID,
Category = Case when Len(Category) > 22 then substring (Category,1,22) +
'...' else Category end
...'
Thanks,
Tom

>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OetZFxMMGHA.3272@.tk2msftngp13.phx.gbl...
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eeALK7MMGHA.3936@.TK2MSFTNGP10.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:u$FUL2MMGHA.2316@.TK2MSFTNGP11.phx.gbl...
> How do I do that if my string has single quotes in it?
Got it.
I just add another single quote next to each single quote and it works fine.
Thanks,
Tom
> For example:
> Select @.Search = 'Select distinct Rank=0,l.CompanyID,m.UserID,
> Category = Case when Len(Category) > 22 then substring (Category,1,22)
> + '...' else Category end
> ...'
> Thanks,
> Tom
>
>