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

See inline ===

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....

=== If you are using a # temp table then the table is being created and
dropped from tempdb.  (if not you should be...)
ie select * from  #t

I have set the Db to auto shrink.
=== Is it a multiuser app?  If so I wouldn't.

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't hurt ;-)

Can I change the size using SQL Server Management Studio
=== Yes
 or do  I need to run an SP?
=== No, but possible.

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?
=== I would do it like this...

--EXEC dbo.x
CREATE procedure dbo.x
as
CREATE TABLE [#ControlCentre] (
	[ControlCentreID] [int] IDENTITY (1, 1) NOT NULL ,
	[Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
	[Phone] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
	[Fax] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
	[Display] [bit] NULL CONSTRAINT [DF_ControlCentre_Display]
DEFAULT (1),
	[AgencyID] [int] NULL ,
	CONSTRAINT [PK_CONTROLCENTRE] PRIMARY KEY  CLUSTERED 
	(
		[ControlCentreID]
	)  ON [PRIMARY] 
) ON [PRIMARY]

INSERT INTO #ControlCentre
                      (Location, Phone, AgencyID, Display)
VALUES     (N'aaa', N'123456', 1, 0)

select * from #ControlCentre

drop table #ControlCentre

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??
=== Looking at the execution plan will tell you if it uses the idx or
not.

HTH

Michael M

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
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com