Is it possible to determine which user created a table?

I've come across a table in one of my databases that I don't remember
creating. Is it possible to tell which user created this table?Hello,
sp_help <tableName>
If the user create the table with DBO schema then the owner will be DBO and
it would be tough for you to identify the user who created the table.
Thanks
Hari
"Danielle" <wxbuff@.aol.com> wrote in message
news:1176065707.471519.177860@.p77g2000hsh.googlegroups.com...
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
>|||Thanks Hari -
You were right... DBO is the owner. Not much to go on I'm afraid, but
that is a handy SP. Thanks for the tip.
Danielle|||On Apr 9, 2:00 pm, "Danielle" <wxb...@.aol.com> wrote:
> Thanks Hari -
> You were right... DBO is the owner. Not much to go on I'm afraid, but
> that is a handy SP. Thanks for the tip.
> Danielle
If you are on SQL 2005 you can use DDL triggers. I do this to track
all schema changes. You can look in "DDL Triggers" in BOL. I know when
my developers change tables, procs, views, fields etc.
Kristina|||On Apr 8, 4:55 pm, "Danielle" <wxb...@.aol.com> wrote:
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
You can also use the information_schema.tables view
run this but change 'YourTableName' to the actual table name
select table_schema as ObjectOwner,table_name,*
from information_schema.tables
where table_name ='YourTableName'
Denis the SQL Menace
http://sqlservercode.blogspot.com/sql

Is it possible to determine which user created a table?

I've come across a table in one of my databases that I don't remember
creating. Is it possible to tell which user created this table?Hello,
sp_help <tableName>
If the user create the table with DBO schema then the owner will be DBO and
it would be tough for you to identify the user who created the table.
Thanks
Hari
"Danielle" <wxbuff@.aol.com> wrote in message
news:1176065707.471519.177860@.p77g2000hsh.googlegroups.com...
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
>|||Thanks Hari -
You were right... DBO is the owner. Not much to go on I'm afraid, but
that is a handy SP. Thanks for the tip.
Danielle|||On Apr 9, 2:00 pm, "Danielle" <wxb...@.aol.com> wrote:
> Thanks Hari -
> You were right... DBO is the owner. Not much to go on I'm afraid, but
> that is a handy SP. Thanks for the tip.
> Danielle
If you are on SQL 2005 you can use DDL triggers. I do this to track
all schema changes. You can look in "DDL Triggers" in BOL. I know when
my developers change tables, procs, views, fields etc.
Kristina|||On Apr 8, 4:55 pm, "Danielle" <wxb...@.aol.com> wrote:
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
You can also use the information_schema.tables view
run this but change 'YourTableName' to the actual table name
select table_schema as ObjectOwner,table_name,*
from information_schema.tables
where table_name ='YourTableName'
Denis the SQL Menace
http://sqlservercode.blogspot.com/

Is it possible to determine which user created a table?

I've come across a table in one of my databases that I don't remember
creating. Is it possible to tell which user created this table?
Hello,
sp_help <tableName>
If the user create the table with DBO schema then the owner will be DBO and
it would be tough for you to identify the user who created the table.
Thanks
Hari
"Danielle" <wxbuff@.aol.com> wrote in message
news:1176065707.471519.177860@.p77g2000hsh.googlegr oups.com...
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
>
|||Thanks Hari -
You were right... DBO is the owner. Not much to go on I'm afraid, but
that is a handy SP. Thanks for the tip.
Danielle
|||On Apr 9, 2:00 pm, "Danielle" <wxb...@.aol.com> wrote:
> Thanks Hari -
> You were right... DBO is the owner. Not much to go on I'm afraid, but
> that is a handy SP. Thanks for the tip.
> Danielle
If you are on SQL 2005 you can use DDL triggers. I do this to track
all schema changes. You can look in "DDL Triggers" in BOL. I know when
my developers change tables, procs, views, fields etc.
Kristina
|||On Apr 8, 4:55 pm, "Danielle" <wxb...@.aol.com> wrote:
> I've come across a table in one of my databases that I don't remember
> creating. Is it possible to tell which user created this table?
You can also use the information_schema.tables view
run this but change 'YourTableName' to the actual table name
select table_schema as ObjectOwner,table_name,*
from information_schema.tables
where table_name ='YourTableName'
Denis the SQL Menace
http://sqlservercode.blogspot.com/

Is it possible to convert T-SQL 2005 to T-SQL 2000

I am working with the Web Service Software Factory and I have created my stored procedures that I will be needing. The problem is that when I try to apply the SP's to the database, I receive errors regarding the syntax. I am using SQL Server 2000 and it generates the script in 2005 T-SQL format. The script in 2005 is quite different from 2000 and I am not sure if converting it is even possible (eg. no try catch equivalent in 2000 and new error handling ). I searched msdn to check if I could specify the script version in WSSF, but I could not find anything. Any suggestions as to how I might solve this without purchasing SQL Server 2005?

The Web Service Software Factory had created this sql file that included the following function that is used throughout the file:

Code Snippet

IF NOT EXISTS (SELECT NAME FROM dbo.sysobjects WHERE TYPE = 'P' AND NAME = 'RethrowError')

BEGIN

EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')

END

GO

ALTER PROCEDURE RethrowError AS

/* Return if there is no error information to retrieve. */

IF ERROR_NUMBER() IS NULL

RETURN;

DECLARE

@.ErrorMessage NVARCHAR(4000),

@.ErrorNumber INT,

@.ErrorSeverity INT,

@.ErrorState INT,

@.ErrorLine INT,

@.ErrorProcedure NVARCHAR(200);

/* Assign variables to error-handling functions that

capture information for RAISERROR. */

SELECT

@.ErrorNumber = ERROR_NUMBER(),

@.ErrorSeverity = ERROR_SEVERITY(),

@.ErrorState = ERROR_STATE(),

@.ErrorLine = ERROR_LINE(),

@.ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

/* Building the message string that will contain original

error information. */

SELECT @.ErrorMessage =

N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +

'Message: '+ ERROR_MESSAGE();

/* Raise an error: msg_str parameter of RAISERROR will contain

the original error information. */

RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1,

@.ErrorNumber, /* parameter: original error number. */

@.ErrorSeverity, /* parameter: original error severity. */

@.ErrorState, /* parameter: original error state. */

@.ErrorProcedure, /* parameter: original error procedure name. */

@.ErrorLine /* parameter: original error line number. */

);

GO

And the error I receive is this ( I added 'machine\instance' to make it more generic):

Code Snippet

Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 4
'ERROR_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 19
'ERROR_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 30
'ERROR_MESSAGE' is not a recognized function name.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 29
Line 29: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 37
Line 37: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 41
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 44
Incorrect syntax near the keyword 'END'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 31
Line 31: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 40
Line 40: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 44
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 47
Incorrect syntax near the keyword 'END'.

The other errors involving the try-catch and end revolve around the try-catch syntax where it does not recognize the rethrowerror function.

Code Snippet

BEGIN TRY

Do Something

END TRY

BEGIN CATCH

EXEC RethrowError;

END CATCH

Any suggestions as to how I could convert this without having to rewrite the sql file generated the project tools? Thanks in advance.

You will have to remove the exception handling code (it is new in SQL Server 2005). You need to also remove references to the ERROR* functions. Only @.@.ERROR was available before.|||That seems so easy. I cant believe I didnt think of that on my own! Thanks for your help.

Is it possible to change the operator of an expression at run time?

I have a report I have created in local mode, in a Winform ReportViewer using VB.net.

Is it possible to change the operator of an expression at run time? That is, I have a filter on a list that looks like this:

Expression: =Fields!InvNum.Value

Operator: =

Value: =Parameters!InvNum.Value

It is possible in code to change the operator from = to >= at the time I run the report? If so, what is the syntax? What I would like to do (don't know it is possible) is to have the operator set to >= at the time the the report is run and then set it back to = when a user selects a specific value for a Parameter for the report. Is this possible?

I suppose I can set it in the load event of the form that contains the ReportViewer, but if this is possible I have not been able to discover the syntax.

Anyone?

You can't change the filter operator at run-time. But you can change the filter expression to =IIF(<your condition>, Fields!InvNum.Value=Parameters!InvNum.Value, Fields!InvNum.Value>=Parameters!InvNum.Value), and the filter value to =true.|||

Thank you for responding.

It's not clear to me what you're saying. I mean I understand that you can change the filter expression as a whole (and not the filter operator) and I understand that you can do that with an Immediate IF statement, but where?

Are you saying that you can change the expression in code, at run time? If so, how and Where, specifically?

Or, are you saying that in the Filter tab of the List component that you can enter an IIF there? If so, what kind of value would I put in <your condition>?

It may be that I am asking a question that seems illogical to you, like "How is time?". But to me, what I am trying to do is pretty common. I am trying to figure out a way to bring lots of information into a report and then give the user the ability to whittle it down if he wants to.

|||

OK. I think I understand some of this. I created an additional string parameter for the report called IWantToSeeAllRows. I set the default value to Y. Then in the filter tab of the list, in the expression column I typed the following:

=IIF(Parameters!IWantAllRows.Value = "Y", Fields!InvNum.Value>=Parameters!InvNum.Value, Fields!InvNum.Value=Parameters!InvNum.Value)

After typing the above RS put an = character in the Operator column and <Blank> in the Value column of the grid in the filter tab.

When running the report I get an error of "Cannot compare data of types system boolean and system string. Please check the data type returned by the filter expression.

I am really guessing here as to where just exactly to place the code etc., but the documentation that I've found on the matter is not explicit for this particular issue. What am I missing?

|||

Most likely you changed the filter value expression to a constant value like TRUE (which is interpreted as string - hence the type mismatch).

Change the filter value expression to =True (which evalutes to a boolean)

-- Robert

Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see
a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunk
s
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearl
y
will reduce the auto-grow events, and slash query execution time. Yes, I ca
n
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transactio
n
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Robyou would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQ
L
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect
,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can se
e a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chu
nks
> together, or even not have to wait between chunks? If the insertion cause
s
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clea
rly
> will reduce the auto-grow events, and slash query execution time. Yes, I
can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transact
ion
> log.
> Is it possible to change the initial size of a database transaction log?
I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is usin
g
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such a
s
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transactio
n
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...|||You have autoshrink on, and say that there is a problem with the grow. It is
like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in t
he foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it n
eed to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/in...dont_shrink.asp
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQ
L
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect
,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can se
e a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chu
nks
> together, or even not have to wait between chunks? If the insertion cause
s
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clea
rly
> will reduce the auto-grow events, and slash query execution time. Yes, I
can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transact
ion
> log.
> Is it possible to change the initial size of a database transaction log?
I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is usin
g
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such a
s
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transactio
n
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob

Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunks
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
will reduce the auto-grow events, and slash query execution time. Yes, I can
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transaction
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Robyou would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...|||You have autoshrink on, and say that there is a problem with the grow. It is like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in the foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it need to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
for elaboration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob

Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunks
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
will reduce the auto-grow events, and slash query execution time. Yes, I can
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transaction
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Rob
you would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...
|||You have autoshrink on, and say that there is a problem with the grow. It is like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in the foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it need to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob