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 >