[dba-SQLServer] Temporary Table - Impact on SQL Db size

pcs at azizaz.com pcs at azizaz.com
Sun Dec 2 20:37:43 CST 2007


Michael,
Thanks for reply.
I user drop and insert into for the temp table and truncate
for the 'live' table. SP via ADODB connection and with
BeginsTrans - CommitTrans and RollBackTrans .... the temp
table takes up about 30Mb - I ran procedure to create and drop
the temp table 10 times .... The .mdf and .ldf did not
increase in size.... So far so good....

I have set the Db to auto shrink. I understand that on Simple
Full Recovery backup the Log File will be truncated
automatically following a backup .... 

So once the Db is placed in production you recommend to
increase the size to allow for say a year's growth....

Can I change the size using SQL Server Management Studio or do
 I need to run an SP?

In regards to the Insert Into SP that creates the temp
table.... It's a mirrored table of the 'live ' one but with no
indexes..... Can I just tag a create PK index to the end of
the Insert Into SP or do I need to run it separate?

The PK is a clustered index on two columns.

When filtering on just one of the columns is the PK clustered
index good for that, or do I need to create an index on each
of the two columns that make up the PK to get best performance??

Regards
Borge  


---- Original message ----
>Date: Mon, 3 Dec 2007 09:35:37 +1100
>From: "Michael Maddison" <michael at ddisolutions.com.au>  
>Subject: Re: [dba-SQLServer] Temporary Table - Impact on SQL
Db size  
>To: <dba-sqlserver at databaseadvisors.com>
>
>Hi Borge,
>
>You can create a job to shrink then db and/or the log file.
>You must backup the files before you can shrink the db file
and truncate
>the log file.
>
>Data Files.
>SQL will grow the data file when it needs to based on the
parameters you
>set when the db is created, by
>default the value is 10%.  Growing the db is 'expensive' and
will lock
>the db while it occurs.  A common practice
>is to set the start size at the size required for 1 years
activity and
>grow it if required during downtime.
>
>Log Files.
>Log transactions in the db and can grow very fast.  Backup
and truncate
>as required.
>
>The above is a very simplistic explanation of how SQL manages
its files.
>In general DBA's don't worry too much
>about db size, they just keep adding more disks ;-)  Logs size is
>managed because its part of the backup routine.
>
>HTH
>
>
>Michael M
>
>Subject: [dba-SQLServer] Temporary Table - Impact on SQL Db size
>
>Hi all,
>
>SQL2005 :
>If I daily create a table, populate it with a bunch of data,
use it to
>perform some updates and then drop it - will this just keep
growing the
>database or will SQL Server manage the disk space.... I am
asking the
>question as my experience with Access Dbs is that ever so
often you had
>to compact the Db if you did something like this.... Does SQL
Server
>handle this better, i.e. without user intervention?
>
>Regards
>Borge
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>



More information about the dba-SQLServer mailing list