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!

Dynamic table name in sp?

Hi,

I need to create a stored procedure that accepts a string parameter containing a table name and truncates the table if it exists and creates it if it doesn't. Is there a way to do this without creating the whole sp in a string and then executing it?

For example, I can do this:

DECLARE @.ThisQuery VARCHAR(8000)

SET @.ThisQuery = 'Create Table ' + @.TableName <--this is the parameter

SET @.ThisQuery = @.ThisQuery + '...'

EXEC (@.ThisQuery)

Is there a better way that does not involve creating a dynamic query like this?

Thanks

I would convert the varchar(8000) to varchar(max); otherwise I would be converned that you might overflow your variable. Yes, this looks like it will work. As an aside I would personally try to avoid this type of generic operation.|||

Using Dynamic SQL for schema change issues is extremely dangerous. First, there would have to be a high level of permission to accomplish the task, and second, it could leave your server open to SQL Injection attacks.

You would be 'safer' using [ sp_executesql() ], and I recommend using unicode, so [ nvarchar(max) ] would be in order.

You may find this article by Erland worth the time to read.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Thank you for this help. Yes, I understand it's dangerous, that's why I'm looking for another way to do it! The article seems great, but it is very long and verbose and after looking through it for a while now and I'm still not sure if it can be done and how. Is there possibly just some syntax someone can please give me that cuts through all this? I don't really need to understand it, I just need to know how to do it.

Many thanks.

|||

Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin

|||DECLARE @.ThisQuery VARCHAR(8000)
declare @.TableName sysname
set @.TableName = 'MyTab'
SET @.ThisQuery = 'if object_ID('''+@.TableName +''') is null '+ CHAR(13)+
'Create Table ' + @.TableName + '([Dummy] varchar(50))'
EXEC (@.ThisQuery)

|||You realize, I hope, that TRUNCATE TABLE will FAIL if there are PK-FK relationships.|||

>> Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin<<

100% agree with you here. Building tables in a stored procedure is generally a sign of using SQL in a non-preferred manner. We haven't been given any insight into the problem trying to be solved, but the "dummy" column makes it seem probably worse than it might actually be.

|||

I have to say, point well taken! After re-thinking my approach, I came up with a better way to do things that does not require a dynamic table name. Thanks.

dynamic SQL as report source

I would like to use a stored procedure with parameters which creates and
executes dynamic sql as a source for a report.
I get an error, though, stating
"could not generate a list of fields for the query".
I tried adding the fields manually to the report definition and matching
them with the fields in the dynamic SQL, but I received error messages like:
"The value expression for the textbox 'blah' refers to the field 'blah'.
Report item expressions can only refer to fields within the current data set
scope. ..."
I guess I can just put all the sql into the dataset directly, but it would
be easier to manage in a stored procedure.
It needs to be dynamic because I have to build a dynamic Order By clause
with different alternative data types, so I can't use a Case statement.
The stored procedure is something like this:
Create Procedure sp
@.param1 int,
@.param2 int
as
declare @.sqlString (varchar(200))
set @.sqlString = 'select col1, col2
from tbl1
where col1 = ' + @.param1 +
'col2 = ' + @.param2
exec @.sqlString
Thanks!
Billwhat degree of dinamic is the order by clause you are making ... I mean, it
ends up being 3 or 4 cases, or the caller has complete control which column
to order?
Why do I say that? ... well, if there are 3 or 4 cases, u can put the static
part of the query in a user defined function, and have 3 or 4 cases where u
return a select of the function with a static order clause ...
Other than that, I cant think of anything ...
"bill" wrote:
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>|||Yes you have to add them manually or type them each time...
"bill" <belgie@.datamti.com> wrote in message
news:eHX3q2CnEHA.3684@.TK2MSFTNGP10.phx.gbl...
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>