Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Auto Shrink - pros & cons

Hi all,
Can anyone give me some info/feedback on any experiences
with the autoshrink feature on a Db.
We need to exercise more control on the Db's &
environments we have and it would seem that most of our
Db's ( & Log files ) are only using a fraction of their
related disk files, so we want to claim back the space for
other projects, but we are wary of 'auto' features on any
product !!!
Thanks in advance.
jHi Keith,
Thanks for the info, i was opposed to setting
the 'autoshrink' feature as i had read that it was very
processor intensive when it kicked in and would obviously
adversly affect performance.
So your suggestion is that on 'production' db's it's best
to manually reclaim space but to let the Db's grow
automatically?, (which sounds a whole lot safer to me).
Is there a large overheard to setting your Db to grow
automatically at say 1 to 5%,(i suppose it depends on the
volume of data that is being inserted into the Db & the
size of the Db to begin with ?).
Thanks in advance & for your previous reply.
j
>--Original Message--
>I understand your concern with "auto" features.
>Feel free to read up on some of the following commands
within Books Online (within the SQL Server program group):
>dbcc shrinkfile
>dbcc shrinkdb
>Most times you would not want to shrink space -- since
the database may need to grow again to store additional
data -- but if you need the space for other projects, I
would start with the commands that I listed.
>--
>Keith, SQL Server MVP
>"Jim A" <jim.armsworth@.lombard.lu> wrote in message
news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
>> Hi all,
>> Can anyone give me some info/feedback on any
experiences
>> with the autoshrink feature on a Db.
>> We need to exercise more control on the Db's &
>> environments we have and it would seem that most of our
>> Db's ( & Log files ) are only using a fraction of their
>> related disk files, so we want to claim back the space
for
>> other projects, but we are wary of 'auto' features on
any
>> product !!!
>> Thanks in advance.
>> j
>.
>|||If you need to reclaim space on a production box, yes, perform the steps =manually. However, I do not feel that it is necessary to attempt to =reclaim space within a production server because the database will most =likely need this space again -- and it will have to grow to reclaim the =space.
However, if you have lots of unused space within your databases and you =need to reclaim disk space for other databases/projects you might want =to look into reclaiming disk space. You might also be able to add disks =to the server, but this would have a cost associated with it.
Regarding the overhead question, there is some overhead involved when =SQL Server grabs more disk space during its autogrow. This overhead is =not any more than you would experience if you performed the growth =yourself. The only advantage of doing it yourself is that you can =schedule it for off hours. The negative is that you might run out of =room within the database during the production day if you forget to =monitor the available free space. -- Keith, SQL Server MVP
"Jim A" <jim.armsworth@.lombard.lu> wrote in message =news:094101c34a18$be49e450$a401280a@.phx.gbl...
> Hi Keith,
> > Thanks for the info, i was opposed to setting > the 'autoshrink' feature as i had read that it was very > processor intensive when it kicked in and would obviously > adversly affect performance. > > So your suggestion is that on 'production' db's it's best > to manually reclaim space but to let the Db's grow > automatically?, (which sounds a whole lot safer to me).
> > Is there a large overheard to setting your Db to grow > automatically at say 1 to 5%,(i suppose it depends on the > volume of data that is being inserted into the Db & the > size of the Db to begin with ?).
> > Thanks in advance & for your previous reply.
> > j
> > > >--Original Message--
> >I understand your concern with "auto" features. > >Feel free to read up on some of the following commands > within Books Online (within the SQL Server program group):
> >dbcc shrinkfile
> >dbcc shrinkdb
> >
> >Most times you would not want to shrink space -- since > the database may need to grow again to store additional > data -- but if you need the space for other projects, I > would start with the commands that I listed.
> >
> >-- > >Keith, SQL Server MVP
> > > >"Jim A" <jim.armsworth@.lombard.lu> wrote in message > news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> >> Hi all,
> >> > >> Can anyone give me some info/feedback on any > experiences > >> with the autoshrink feature on a Db. > >> > >> We need to exercise more control on the Db's & > >> environments we have and it would seem that most of our > >> Db's ( & Log files ) are only using a fraction of their > >> related disk files, so we want to claim back the space > for > >> other projects, but we are wary of 'auto' features on > any > >> product !!!
> >> > >> Thanks in advance.
> >> > >> j
> >.
> >|||Hi Jim,
I think the following link would be of some interest to you. It does a good
job of explaining how auto shrink works and what to expect if you use this
feature.
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
Arvind Ranasaria
SQL Server Development
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Jim A" <jim.armsworth@.lombard.lu> wrote in message
news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> Hi all,
> Can anyone give me some info/feedback on any experiences
> with the autoshrink feature on a Db.
> We need to exercise more control on the Db's &
> environments we have and it would seem that most of our
> Db's ( & Log files ) are only using a fraction of their
> related disk files, so we want to claim back the space for
> other projects, but we are wary of 'auto' features on any
> product !!!
> Thanks in advance.
> j|||Arvind,
Is that an internal viewer? The link does not work for me.
--
Andrew J. Kelly
SQL Server MVP
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:O6ldvFjSDHA.3188@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I think the following link would be of some interest to you. It does a
good
> job of explaining how auto shrink works and what to expect if you use this
> feature.
>
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
> Arvind Ranasaria
> SQL Server Development
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "Jim A" <jim.armsworth@.lombard.lu> wrote in message
> news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> > Hi all,
> >
> > Can anyone give me some info/feedback on any experiences
> > with the autoshrink feature on a Db.
> >
> > We need to exercise more control on the Db's &
> > environments we have and it would seem that most of our
> > Db's ( & Log files ) are only using a fraction of their
> > related disk files, so we want to claim back the space for
> > other projects, but we are wary of 'auto' features on any
> > product !!!
> >
> > Thanks in advance.
> >
> > j
>|||Hi Andrew,
Sorry for the late response. Last couple of days had been hectic. Here is a
copy of the article that I referred to.
**Problem** SQL Svr Enterprise Edtn 2000 [2000 -W_2392 ]
ID: SOX010828700125 CRT: Aug 28 2001 MOD: Aug 28 2001 STS:Customer Verified
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Problem Description ***
This is a source code analysis on how auto shrink (auto_shrink, autoshrink)
works
on SQL Server 2000.
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Resolution *** Aug 28 2001 3:27PM jackli
Here is my source code research, testing with profiler findings in order to
answer
customer's question in log ( *** Log # 39).
Here is how auto shrink works:
1) When SQL Server starts, it Starts StartGlobalTaskProc which starts a
Timer
Object which set intervals of TaskAutoShrink.
2) Then we set auto shrink task to kick within 5 mins. (const
SHORT_SHRINK_PERIOD =5*60; // seconds)
3) All sebequent auto shrink will be 30 min (const SHRINK_PERIOD = 30*60;).
The
Timer is only resposible to queue the work item.
But there are two very important findings that I will explain right after
this.
There are 3 factors that may affect the effectiveness of shrink
1) DB not set to auto shrink. We simply do not do that.
2) We don't do shrinking on these databases with these conditions either
DBT_RDONLY | DBT_OFFLINE | DBT_SUSPECT | DBT_DETACHED | DBT_USE_NOTREC |
DBT_SHUTDOWN)
4) We set up a timer to queue the ShrinkTask for the database.
Two very important facts:
1) We take round robin to make an attempt to shrink a database which has
auto
shrink set on. Basically, we are make a sweep on database for 30 min
intervals.
Example: Say if pubs (5), northwind (6) and testdb (7) are set to auto
shrink, it
will take 1 and half hours before it will try to shrink pubs again. As you
can see
it will have impact on server with large number of databases
Round robin always starts with master during startup even though you can't
really
set auto shrink on master.
2) Profiler trace will only capture when an shrink is actually taken place
(not
only attempting). Because profiler generate Datbase shrinking events in the
FileMgr::ShrinkFile code. The fact that Timer sets the shrink the db doesn't
mean
it will be successfully run.
I found out a trace flag 662. This trace flag 662 will log an entry when
shrinking is attempted meaning scheduled work triggered. It will log entry
of
"Autoshrink database Dbid <db_id>". But it will log ghost message very
frequently.
It will fill out error log with theses messages
2001-08-28 16:10:41.37 spid8 Suspending task manager. Currently queued: 1
2001-08-28 16:10:51.37 spid8 Continuing ghost cleanup processing. System
idle.
Currentlly queued: 1
every 30 min you will get this in errorlog:
2001-08-28 16:11:01.39 spid11 Autoshrink database Dbid 12
I used this to do a test for 2 hours and found out the output to support the
above
claim. (see more in the details).
Now, customer's 128 databases are by design behavior. It means it will take
at
least 64 hours before we even attempt to shrink last database if every db is
set to
auto shrink.
Workaround would be:
1) use dbcc shrinkdatabase scheduled
2) He can turn off auto shrink on all databases that are not necessary. This
will
short the cycle.
3) You can use this script detect how many db are like that on customer's
server.
select case when status & 4194304 = 0 then 'No' else 'yes' end as 'auto
shrink', *
from sysdatabases order by dbid
I put this into an SO in case some one or myself needs the analysis. I will
contact CPR to see if a KB is necessary.
</TL:Summary>
<TL:ACTION_PLAN>
</TL:ACTION_PLAN>
<TL:DetailedNotes>
note: you don't have to read this part if you don't want to:
StartGlobalTaskProc
<<\\cprsrcwa\sql\sql80.rtm\ntdbms\storeng\dfs\access\access.c>>
class TaskAutoShrink : public TaskReqPkt
<<\\cprsrcwa\sql\sql80.RTM\ntdbms\ntinc\dbcc.h>>
class TaskAutoShrink : public TaskReqPkt
<<\\cprsrcwa\sql\sql80.RTM\starfter\sqltrace\comnevnt\resource\events.rc>>
DATAFILE_AUTOSHRINK_EVENT_CLASS
BOOL
FileMgr::ShrinkFile
TaskAutoShrink::ProcessTskPkt ()
Trace flag 662 (ACCESS,TRCFLG_ACCESS_GHOSTPAGE_REMOVED)
TaskAutoShrink::SetNextDb gets next elligible db for shrinking
Call tree
Sqlservr_main
StartUp::StartDBMSThread
StartGlobalTaskProc
TimerTask
CTimerTask::Execute (submits autoshrink task).
TaskReqPkt::Submit
</TL:DetailedNotes> </TLREVIEW>
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Resolution *** Mar 15 2002 2:39PM JPilov
--In SQL Server 7.0 there does not seem to be an interval based on the code
research. We seem to simply run through the databases one at a time and do
the
autoshrink.
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
Arvind.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwsMB8jSDHA.3192@.tk2msftngp13.phx.gbl...
> Arvind,
> Is that an internal viewer? The link does not work for me.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:O6ldvFjSDHA.3188@.tk2msftngp13.phx.gbl...
> > Hi Jim,
> >
> > I think the following link would be of some interest to you. It does a
> good
> > job of explaining how auto shrink works and what to expect if you use
this
> > feature.
> >
> >
>
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
> >
> > Arvind Ranasaria
> >
> > SQL Server Development
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "Jim A" <jim.armsworth@.lombard.lu> wrote in message
> > news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> > > Hi all,
> > >
> > > Can anyone give me some info/feedback on any experiences
> > > with the autoshrink feature on a Db.
> > >
> > > We need to exercise more control on the Db's &
> > > environments we have and it would seem that most of our
> > > Db's ( & Log files ) are only using a fraction of their
> > > related disk files, so we want to claim back the space for
> > > other projects, but we are wary of 'auto' features on any
> > > product !!!
> > >
> > > Thanks in advance.
> > >
> > > j
> >
> >
>

auto logout in sql server

Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.
Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.

auto logout in sql server

Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.

auto logout in sql server

Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
--
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.

Auto key generation

Hi,

Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)

make the column an identity

CREATE TABLE test(id INT IDENTITY, SomeOtherVAlue varchar(50))

INSERT test VALUES( 'A' )
INSERT test VALUES( 'B' )
INSERT test VALUES( 'C' )
INSERT test VALUES( 'D' )
INSERT test VALUES( 'E' )
INSERT test VALUES( 'F' )
GO

SELECT * FROM Test

id SomeOtherVAlue
-- --
1 A
2 B
3 C
4 D
5 E
6 F

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i have to got the answer

Use Identity Specification property of column

Auto increment help

Is there an auto increment feature for integer key fields. I'm using VS2005 to configure my DB and I do not see a way to set this.

Thanks in Advance
MoonWa

CREATE TABLE tblArtists
(
artistID int identity primary key,
artistName nvarchar(50) NOT NULL
)

this SQL creates a table with two fields, the keyword 'identity' makes a field auto increment. In the UI there's a property 'identity specification', or something similar. If you open this, you can check 'is identity'.

|||

DataColumn type has a property AutoIncrement:

dtEmployees.Columns[0].AutoIncrement = true;
dtEmployees.Columns[0].AutoIncrementSeed = -1;
dtEmployees.Columns[0].AutoIncrementStep = -1;

SQL Server table may also have one identity column, as Christian mentioned. But you should be aware of some points:

1. SQL Server by default doesn't allows inserting in Identity columns (and never allows updates)
2. Your DataColumn instance knows nothing about current SQL Server Identity value and will possibly generate duplicate values (which will fail on insert if column is constrained via Primary Key or Unique, as usually).

So, if you let SQL Server to generate values, you also should update your column' values with generated one from stored proc output parameters or from output from FOR INSERT trigger on your table (or so) using DataAdapter. For example:

daEmployees.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

WBR, Evergray
--
Words mean nothing...

|||Thanks for your help.

MoonWa

auto grow

How expensive is auto grow when it it kicks in? We're planing on monitoring database growth and resizing as needed, but want to set this feature as well. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed 50 mb)?
thanks
The larger the database the more expensive it becomes. A small DB growing a
little is of course a small expense. It is better to plan ahead for a
larger DB and try to grow it in advance when it will not effect your users.
Still keep auto grow enabled in case you miss a step but try and keep ahead
of it so it does not happen and tick off your user base.
Jeff Duncan
MCDBA, MCSE+I
"Peter Feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:4F1E0506-78D8-4C7C-B3C2-953C5CCE8874@.microsoft.com...
> How expensive is auto grow when it it kicks in? We're planing on
monitoring database growth and resizing as needed, but want to set this
feature as well. Is it better to set this high (ie 10% on a 5gb database)
or low (ie fixed 50 mb)?
> thanks
|||You can find these issues addressed and suggestions on using
autogrow in the following article:
INF: Considerations for Autogrow and Autoshrink
Configuration
http://www.support.microsoft.com/?id=315512
-Sue
On Mon, 3 May 2004 07:56:11 -0700, "Peter Feakins"
<anonymous@.discussions.microsoft.com> wrote:

>How expensive is auto grow when it it kicks in? We're planing on monitoring database growth and resizing as needed, but want to set this feature as well. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed 50 mb)?
>thanks

auto grow

How expensive is auto grow when it it kicks in? We're planing on monitoring
database growth and resizing as needed, but want to set this feature as well
. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed
50 mb)?
thanksThe larger the database the more expensive it becomes. A small DB growing a
little is of course a small expense. It is better to plan ahead for a
larger DB and try to grow it in advance when it will not effect your users.
Still keep auto grow enabled in case you miss a step but try and keep ahead
of it so it does not happen and tick off your user base.
Jeff Duncan
MCDBA, MCSE+I
"Peter Feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:4F1E0506-78D8-4C7C-B3C2-953C5CCE8874@.microsoft.com...
> How expensive is auto grow when it it kicks in? We're planing on
monitoring database growth and resizing as needed, but want to set this
feature as well. Is it better to set this high (ie 10% on a 5gb database)
or low (ie fixed 50 mb)?
> thanks|||You can find these issues addressed and suggestions on using
autogrow in the following article:
INF: Considerations for Autogrow and Autoshrink
Configuration
http://www.support.microsoft.com/?id=315512
-Sue
On Mon, 3 May 2004 07:56:11 -0700, "Peter Feakins"
<anonymous@.discussions.microsoft.com> wrote:

>How expensive is auto grow when it it kicks in? We're planing on monitoring
database growth and resizing as needed, but want to set this feature as wel
l. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixe
d 50 mb)?
>thanks