Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Dimensional Arrays in MSSQL 2000

I am converting a database from Clarion to MSSQL 2000. In the Clarion files we use dimensional arrays. I've seen dimensional arrays mentioned on the forum, but can't find how to set one up in MSSQL 2000. Can anyone help me find some kind of documentation on this, or point me in the right direction?

MartyRefer to this Clarion link (http://www.clarionmag.com/cmag/v1/v1n4convertingtosql.html) for information and also refer to books online for 'sp_OAMethod' and other referenced topics.

HTH|||honea, not sure I know what dimesional arrays are with regards to Calrion. As best as I remember Clarion used flat files. If you can provide an explination I will be glad to help if I can.|||Satya,

Thank you for the response. I did look up the 'sp_OAMethod', and while it talks about referencing the arrays, I have found no place that talks about actually setting the array up. How do I put an array in the table?

Marty|||Paul,

In Clarion you would declare a field type and then dimension it. Example:

mydimlong Long Dim(2,36)

This would give you a long data type that has 72 possible places to store data. You would then retrieve or put data in by referencing it like this...

mydimlong[1,1] = 'Male'
mydimlong[2,1] = 'Female'
mydimlong[1,2] = somedata
mydimlong[2,2] = somemoredata

ETC...

Marty|||To my klnowledge there is nothing comperable in MSSQL Server. I would probably set this up as a child table and move on.|||Thank you Paul,

I will probably keep the table in clarion for now.

Marty

Differential Backups Produce 55GB Files

We have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.

I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.

Has anyone experienced this issue before?
What causes it?
How do we resolve it?

Thank you in advance for your help,
Greg
if the log backups occurs every 1 hr you dont need to schedule the differential backup.......moreover check if any process is running at the time of backup......generally if you schedule the backups frequently the log files shudnt grow as high in your case........|||

I'm not worries about the size of the log files. The logs are fine. This post regards the size of the differential backup file itself (55GB). We don't really need it, true, but its much easier to restore one diff backup over six logs in my humble opinion, but this is really beside the point.

|||Was there any unusual activity over the weekend? Someone doing a bunch of bulks loads or index rebuilds for instance?|||Are you appending your differential backups to the same file, or creating a new file for each differential backup?|||Yes. Over the weekend we usually do an index rebuild, and some heavy inserts. Our database is mirrored as well, so we're not able to actually do the heavy inserts as a true bulk insert.|||Each backup creates a new file. A secondary process on another machine copies the files over to another server.|||

most probably this is coz of the activities in the weekend. When u rebiult index it take extraspace to create the fresh index . the existing index are not overwritten beacuse if system needs the existing index to rollback if necessary. Differential backup take the backup of all the extents changed from last full backup. So what i would prefer is... take full backup after the weekend maintenance instead of Differential(if you have maintenance window).

Madhu

Differential Backup with RETAINDAYS

I am looking into saving differential backup files for X number of days:

I used the wizzard with RETAINDAYS here is the syntax:

BACKUP DATABASE [Northwind] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\N' WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL , NAME = N'n', SKIP , STATS = 10, FORMAT

When I run it overrides the first file.

My question is should I rename the differential backup file and save it in a different folder. If so how do I do that?

Or do you know of a script that will retain a differential backup for x number of days.

Or do you know of a better solution.

Thanks in advance

Anu AhujaHi Anu,

FYI: The Database Maintenance Wizard does the same for you for Full and Transaction Backups. But for Differential Backup , you have to write a script.

The script below does the backup of the currentdatabase by adding dateand time along with databasename. So this will backup the database with new name everytime.

Example: NorthwindAug231040.bak

/* Script to Backup the database with Date and Time */
/* Owner : Varad01 */
/* Try this script first with Pubs database */

Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin

Select @.dbname=db_name()

Select @.CurrentDateTime =
substring(DATENAME(month, getdate()),1,3) +
cast(DATEPART(day, GETDATE()) as varchar(2))+
cast(DATEPART(hh, GETDATE()) as varchar(2)) +
cast(DATEPART(mi, GETDATE()) as varchar(2))

Select @.dbbackupname = @.dbname+@.CurrentDateTime

select @.dbbackupname ='C:\' + @.dbbackupname + '.bak'

backup database @.dbname to disk=@.dbbackupname WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS = 10, FORMAT

End

Hope this Helps.

Have Fun :)

Varad01
MCDBA,MCSE|||Thank you very much!!!

Anu:)|||Hello again,

The above script is working great, I am able to create a unique name for the differential backup. BUT ......I would like the backup to be retained for 3 days and that is not working.

Do I add a delete?

Thanks in advance.

Anu:confused:|||Hi!

Just a suggestion... why don't you create three separate folders for your three-day retention backup files. With this, you can safely specify different destination paths for each execution. Use the script below:

/* Script using different destination folders per day*/
/* Created by Boysie Jocson, Manila, Phils. */

declare @.day_week int,
@.directory char(80)
set @.day_week = datepart(dw,getdate())
if @.day_week = 1
begin
set @.directory = '\\servername\day1\db_1stdiff.bak'
end
else if @.day_week = 2
begin
set @.directory = '\\servername\day2\db_2nddiff.bak'
end
else if @.day_week = 3
begin
set @.directory = '\\servername\day3\db_3rddiff.bak'
end

BACKUP DATABASE [dbname] TO DISK = @.directory WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'db_diff', SKIP , STATS = 10, DESCRIPTION = N'db differential backup', NOFORMAT

... hope this helps.|||Thank you!! This is working.

Anu|||Thank you!! This is working.

Anu:)|||Originally posted by anu
Thank you!! This is working.

Anu:)

I'm happy to hear that. You're welcome!

Differential Backup Script

I would like to test if the day is Sunday (listed below),
if 'YES' then delete the files in the directory with following critieria
(E:\NewOrleans\*FULL.BAK) and create a new full backup file.
If 'NO' (Sunday) create check to see if a E:\NewOrleans\*FULL.BAK file
exist if 'NO' (E:\NewOrleans\*FULL.BAK) create a new full backup file,
if 'YES' (E:\NewOrleans\*FULL.BAK) then create differential backup.
Please help me modifiy the script listed below to meet the criterias listed
above.
Thanks,
declare @.yyyymmdd varchar(24)
declare @.var5 tinyint
select @.yyyymmdd = convert(varchar(24), getdate(),112) -- yyyymmdd
select @.var5 = datepart(dw,getdate())
if @.var5 = 1 -- Sunday
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
-- Delete full backup listing
Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
-- create full backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
WITH INIT")
-- create diff backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
WITH DIFFERENTIAL")
Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
Volume in drive E is NWORLBD01A Dumps
Volume Serial Number is DRV-73B6
NULL
Directory of E:\NewOrleans
NULL
05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
1 File(s) 168,968,064 bytes
0 Dir(s) 295,359,264 bytes free
NULLHi
You may want to split the functionality into different procedures and use
the scheduler to have one job for Sundays and one job for the rest of the
w! If may be easier to follow the backup wizard to create these jobs.
The day of the w will depend on what DATEFIRST is set to, therefore you
should make sure that you set it or make the conditions cater for the
different days.
WIth the IF statement you can group statements to be run on Sunday with a
BEGIN and END statements e.g. (This is unchecked!)
SET DATEFIRST 7
if @.var5 = 1 -- Sunday
BEGIN
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
-- Delete full backup listing
Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
-- create full backup
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
WITH INIT")
-- create diff backup
END
ELSE
BEGIN
EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
WITH DIFFERENTIAL")
END
John
"Joe K." wrote:

> I would like to test if the day is Sunday (listed below),
> if 'YES' then delete the files in the directory with following critieria
> (E:\NewOrleans\*FULL.BAK) and create a new full backup file.
> If 'NO' (Sunday) create check to see if a E:\NewOrleans\*FULL.BAK file
> exist if 'NO' (E:\NewOrleans\*FULL.BAK) create a new full backup file,
> if 'YES' (E:\NewOrleans\*FULL.BAK) then create differential backup.
> Please help me modifiy the script listed below to meet the criterias liste
d
> above.
> Thanks,
> declare @.yyyymmdd varchar(24)
> declare @.var5 tinyint
> select @.yyyymmdd = convert(varchar(24), getdate(),112) -- yyyymmdd
> select @.var5 = datepart(dw,getdate())
> if @.var5 = 1 -- Sunday
>
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
> -- Delete full backup listing
> Exec master..xp_cmdshell 'del E:\NewOrleans\*FULL.BAK'
> -- create full backup
> EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _FULL.BAK + "'" + "
> WITH INIT")
> -- create diff backup
> EXEC ("BACKUP DATABASE NEWORLEANS TO DISK = " + "'"+ _DIFF.BAK + "'" + "
> WITH DIFFERENTIAL")
>
>
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
>

Differential Backup Files

When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.pretty easy to do via T-SQL statement. The following script creates a backup with date and hour as timestamp in file name.

declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')

Differential backup file almost as big as a full backup

We've been during daily full backups, with the size of the bak files at
slightly under 1.4G. The last full backup was Tuesday night. On Wednesday,
I did my first diffential backup, and the file size was 1.25G.
We are a very small company and there is little daily input. Yesterday, we
might have received a couple of checks, paid a couple bills, ordered a couple
parts, entered a few timecard transactions. So I really expected the
differential backup to show a substantial savings in disk space.
In looking for reasons:
1. The backup plan is put together using the maintenance plan wizard, and
one of the things it does is create separate files that bear the database
name, date and time. Is it possible that Sql 2005 doesn't realize that there
was a previous full backup if the differential is being put in a different
file?
2. As part of the nightly maintenance, I run tasks to check integrity,
rebuid indexes and update statistics. Could these tasks be making database
changes that would therefore cause practically the entire database to be
included in the differential backup, even though the actual data has not
changed?I would suggest that you take a copy of the database and run some backups
manually. Your logic is sound, but your results don't make sense.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||Bev,
Yes, rebuilding indexes updates your database. So every page (or is it
extent) that is updated by the process will be backed up. Doing all of that
nightly is not compatible with getting small differential backups.
With very low velocity of data change, the nightly index rebuilds should not
be necessary.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||That is just what my tests have shown me. I have removed that task from the
plan and hope to see better results in the morning. There was no particular
reason I was doing that anyway, just an erroneous belief that you can't have
too much of a good thing.
"Russell Fields" wrote:
> Bev,
> Yes, rebuilding indexes updates your database. So every page (or is it
> extent) that is updated by the process will be backed up. Doing all of that
> nightly is not compatible with getting small differential backups.
> With very low velocity of data change, the nightly index rebuilds should not
> be necessary.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> > We've been during daily full backups, with the size of the bak files at
> > slightly under 1.4G. The last full backup was Tuesday night. On
> > Wednesday,
> > I did my first diffential backup, and the file size was 1.25G.
> > We are a very small company and there is little daily input. Yesterday,
> > we
> > might have received a couple of checks, paid a couple bills, ordered a
> > couple
> > parts, entered a few timecard transactions. So I really expected the
> > differential backup to show a substantial savings in disk space.
> >
> > In looking for reasons:
> > 1. The backup plan is put together using the maintenance plan wizard, and
> > one of the things it does is create separate files that bear the database
> > name, date and time. Is it possible that Sql 2005 doesn't realize that
> > there
> > was a previous full backup if the differential is being put in a different
> > file?
> >
> > 2. As part of the nightly maintenance, I run tasks to check integrity,
> > rebuid indexes and update statistics. Could these tasks be making
> > database
> > changes that would therefore cause practically the entire database to be
> > included in the differential backup, even though the actual data has not
> > changed?
>
>

Differente between WebServices ReportingService, ReportingServiceExecution and ReportingService

I see those asmx files on the reportserver virtual folder and 2 of them have the render method, which are the difference between them? when should I use one or another?ReportService.asmx -- The "old" endpoint for 2000. It combines both management and execution functions.

different file size

Hi
I find strange thing. I have (MSSQL2000) big database (about 90 GB). In this
database I have to files with about 10.000.000 and 30.000.000 records. I was
copy this tables to other serwer, to new created database. After it I create
all of indexes, constraint ect on this tables in new database. On views with
this tables there are no indexes. The size of new database is about 10GB.
After it I shrink, backup with truncate log and shring again source
database. It was shrink to 55GB (over 25GB!!!). After I return with this two
tables and indexes to source database file size grow to 90GB!!!
WHY?
MArek
www.programowanieobiektowe.plIt sounds like you moved two tables out of one database into an empty
database.
This new (empty) database grew to 10GB with the new tables.
You removed the two tables from your existing database and shrank the
database. Lots of disk space was returned to the OS.
Then you added the tables back to the original database causing it to grow.
You expected the database to grow around 10GB but it grew by much more.
How did you move the data?
Check the log file? Did it grow when you added the tables to the original
database?
I am guessing that auto growth caused your data and log files to grow a
little more than necessary. This is ok, as it leaves "room" in your
database for new data as it comes in.
You could try using the stored procedure sp_spaceused within your database
to take a look at the space consumed by data and indexes as well as the
amount of unused space within your database.
Keith Kratochvil
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:edmp7m$8gq$1@.news2.ipartners.pl...
> Hi
> I find strange thing. I have (MSSQL2000) big database (about 90 GB). In
> this database I have to files with about 10.000.000 and 30.000.000
> records. I was copy this tables to other serwer, to new created database.
> After it I create all of indexes, constraint ect on this tables in new
> database. On views with this tables there are no indexes. The size of new
> database is about 10GB. After it I shrink, backup with truncate log and
> shring again source database. It was shrink to 55GB (over 25GB!!!). After
> I return with this two tables and indexes to source database file size
> grow to 90GB!!!
> WHY?
> MArek
>
> --
> www.programowanieobiektowe.pl|||Be sure to run DBCC UPDATEUSAGE(0) before checking the space consumed
inside a database. Without that the numbers are a bit unreliable.
One issue to consider is that creating a clustered index requires
creating a complete copy of the table. Suppose a database had one
table, size 10GB, and the database was 10GB with no free space. To
add (or re-create) a clustered index on that table requires making a
second copy of the data in the database, so when you are done you have
a 10GB table in a 20GB database.
Roy
On Wed, 6 Sep 2006 17:24:37 +0200, "Marek Wierzbicki"
<marek.wierzbickiiiii@.azymuttttt.pl> wrote:

>Hi
>I find strange thing. I have (MSSQL2000) big database (about 90 GB). In thi
s
>database I have to files with about 10.000.000 and 30.000.000 records. I wa
s
>copy this tables to other serwer, to new created database. After it I creat
e
>all of indexes, constraint ect on this tables in new database. On views wit
h
>this tables there are no indexes. The size of new database is about 10GB.
>After it I shrink, backup with truncate log and shring again source
>database. It was shrink to 55GB (over 25GB!!!). After I return with this tw
o
>tables and indexes to source database file size grow to 90GB!!!
>WHY?
>MArek
>
>--
>www.programowanieobiektowe.pl|||> It sounds like you moved two tables out of one
> database into an empty database.
> This new (empty) database grew to 10GB with the new tables.
> You removed the two tables from your existing
> database and shrank the database. Lots of disk space was returned to the
> OS.
> Then you added the tables back to the original
> database causing it to grow.
> You expected the database to grow around 10GB
> but it grew by much more.
> How did you move the data?
select * into new_table from linkedserver..oldtable
next
create all indexes from script

> Check the log file? Did it grow when you
> added the tables to the original database?
Yes, but its in simple mode, so it have only few MB used

> I am guessing that auto growth caused your data
> and log files to grow a little more than necessary. This is ok, as it
> leaves "room" in your database for new data as it comes in.
You are right - it was auto grow with 35%!!!!!!
But why they cant be shrinked?

> You could try using the stored procedure sp_spaceused within your database
> to take a look at the space consumed by data and indexes as well as the
> amount of unused space within your database.
I will check
Marek|||> Be sure to run DBCC UPDATEUSAGE(0) before checking
> the space consumed
> inside a database. Without that the numbers are
> a bit unreliable.
I will try dio it again and check it

> One issue to consider is that creating a clustered index requires
> creating a complete copy of the table. Suppose a database had one
> table, size 10GB, and the database was 10GB with no free space. To
> add (or re-create) a clustered index on that table requires making a
> second copy of the data in the database, so when you are done you have
> a 10GB table in a 20GB database.
index was created after copy and size didnt grow
I will try it again and describe result
Marek|||Marek Wierzbicki wrote:
> select * into new_table from linkedserver..oldtable
> next
> create all indexes from script
>
> Yes, but its in simple mode, so it have only few MB used
>
> You are right - it was auto grow with 35%!!!!!!
> But why they cant be shrinked?
>
> I will check
> Marek
>
Simple mode will NOT prevent the log file from growing, it will still
grow large enough to hold whatever transaction you're pumping through
it, in this case it was a transaction large enough to hold your entire
table import. See
http://realsqlguy.com/serendipity/a...action-Log.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

different file size

Hi
I find strange thing. I have (MSSQL2000) big database (about 90 GB). In this
database I have to files with about 10.000.000 and 30.000.000 records. I was
copy this tables to other serwer, to new created database. After it I create
all of indexes, constraint ect on this tables in new database. On views with
this tables there are no indexes. The size of new database is about 10GB.
After it I shrink, backup with truncate log and shring again source
database. It was shrink to 55GB (over 25GB!!!). After I return with this two
tables and indexes to source database file size grow to 90GB!!!
WHY?
MArek
--
www.programowanieobiektowe.plIt sounds like you moved two tables out of one database into an empty
database.
This new (empty) database grew to 10GB with the new tables.
You removed the two tables from your existing database and shrank the
database. Lots of disk space was returned to the OS.
Then you added the tables back to the original database causing it to grow.
You expected the database to grow around 10GB but it grew by much more.
How did you move the data?
Check the log file? Did it grow when you added the tables to the original
database?
I am guessing that auto growth caused your data and log files to grow a
little more than necessary. This is ok, as it leaves "room" in your
database for new data as it comes in.
You could try using the stored procedure sp_spaceused within your database
to take a look at the space consumed by data and indexes as well as the
amount of unused space within your database.
--
Keith Kratochvil
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:edmp7m$8gq$1@.news2.ipartners.pl...
> Hi
> I find strange thing. I have (MSSQL2000) big database (about 90 GB). In
> this database I have to files with about 10.000.000 and 30.000.000
> records. I was copy this tables to other serwer, to new created database.
> After it I create all of indexes, constraint ect on this tables in new
> database. On views with this tables there are no indexes. The size of new
> database is about 10GB. After it I shrink, backup with truncate log and
> shring again source database. It was shrink to 55GB (over 25GB!!!). After
> I return with this two tables and indexes to source database file size
> grow to 90GB!!!
> WHY?
> MArek
>
> --
> www.programowanieobiektowe.pl|||Be sure to run DBCC UPDATEUSAGE(0) before checking the space consumed
inside a database. Without that the numbers are a bit unreliable.
One issue to consider is that creating a clustered index requires
creating a complete copy of the table. Suppose a database had one
table, size 10GB, and the database was 10GB with no free space. To
add (or re-create) a clustered index on that table requires making a
second copy of the data in the database, so when you are done you have
a 10GB table in a 20GB database.
Roy
On Wed, 6 Sep 2006 17:24:37 +0200, "Marek Wierzbicki"
<marek.wierzbickiiiii@.azymuttttt.pl> wrote:
>Hi
>I find strange thing. I have (MSSQL2000) big database (about 90 GB). In this
>database I have to files with about 10.000.000 and 30.000.000 records. I was
>copy this tables to other serwer, to new created database. After it I create
>all of indexes, constraint ect on this tables in new database. On views with
>this tables there are no indexes. The size of new database is about 10GB.
>After it I shrink, backup with truncate log and shring again source
>database. It was shrink to 55GB (over 25GB!!!). After I return with this two
>tables and indexes to source database file size grow to 90GB!!!
>WHY?
>MArek
>
>--
>www.programowanieobiektowe.pl|||> It sounds like you moved two tables out of one
> database into an empty database.
> This new (empty) database grew to 10GB with the new tables.
> You removed the two tables from your existing
> database and shrank the database. Lots of disk space was returned to the
> OS.
> Then you added the tables back to the original
> database causing it to grow.
> You expected the database to grow around 10GB
> but it grew by much more.
> How did you move the data?
select * into new_table from linkedserver..oldtable
next
create all indexes from script
> Check the log file? Did it grow when you
> added the tables to the original database?
Yes, but its in simple mode, so it have only few MB used
> I am guessing that auto growth caused your data
> and log files to grow a little more than necessary. This is ok, as it
> leaves "room" in your database for new data as it comes in.
You are right - it was auto grow with 35%!!!!!!
But why they cant be shrinked?
> You could try using the stored procedure sp_spaceused within your database
> to take a look at the space consumed by data and indexes as well as the
> amount of unused space within your database.
I will check
Marek|||> Be sure to run DBCC UPDATEUSAGE(0) before checking
> the space consumed
> inside a database. Without that the numbers are
> a bit unreliable.
I will try dio it again and check it
> One issue to consider is that creating a clustered index requires
> creating a complete copy of the table. Suppose a database had one
> table, size 10GB, and the database was 10GB with no free space. To
> add (or re-create) a clustered index on that table requires making a
> second copy of the data in the database, so when you are done you have
> a 10GB table in a 20GB database.
index was created after copy and size didnt grow
I will try it again and describe result
Marek|||Marek Wierzbicki wrote:
>> It sounds like you moved two tables out of one
>> database into an empty database.
>> This new (empty) database grew to 10GB with the new tables.
>> You removed the two tables from your existing
>> database and shrank the database. Lots of disk space was returned to
>> the OS.
>> Then you added the tables back to the original
>> database causing it to grow.
>> You expected the database to grow around 10GB
>> but it grew by much more.
>> How did you move the data?
> select * into new_table from linkedserver..oldtable
> next
> create all indexes from script
>
>> Check the log file? Did it grow when you
>> added the tables to the original database?
> Yes, but its in simple mode, so it have only few MB used
>> I am guessing that auto growth caused your data
>> and log files to grow a little more than necessary. This is ok, as it
>> leaves "room" in your database for new data as it comes in.
> You are right - it was auto grow with 35%!!!!!!
> But why they cant be shrinked?
>> You could try using the stored procedure sp_spaceused within your
>> database to take a look at the space consumed by data and indexes as
>> well as the amount of unused space within your database.
> I will check
> Marek
>
Simple mode will NOT prevent the log file from growing, it will still
grow large enough to hold whatever transaction you're pumping through
it, in this case it was a transaction large enough to hold your entire
table import. See
http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Different access files DTS to mssql

We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:

\\servername\TA\company\07-25-04\Trans.mdb
\\servername\TA\company\08-08-04\Trans.mdb
\\servername\TA\company\08-22-04\Trans.mdb

The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.

I am just not sure how to do that. Any ideas?Since the structure of each of the .MDB files is the same, it sounds to me like a DTS package using a Global Variable would be a good solution. The Global Variable would be used for the name of the Access file to import. Your VB.NET code would loop through the files to process, feeding DTS the name of each file one by one via a Global Variable.

You might consider keep a SQL table of the files you have processed so your VB.NET program can check against that to see if it has already processed and given file.

Terri|||Sounds good....... how do I do that?|||Okay, I have some code in vb.net to get each date from a startdate. I made a DTS and exported it into a VBS file. How doI get that code to run in my VB.net solution? The vbs file has amain() and some other subs. Just not sure how to referenceit. (I can post the vbs script if needed)

Differences in file sizes sys.database_files and sys.master_files

Hello,
We are seeing a differince in size column for the same files between
sys.database_files and sys.master_files. sys.master_files inidcates smaller
sizes so I think that rules out deferred drop operations. Any ideas? this
is for Tempdb files.
TIA,
All you need is /3GB. Don't worry about /PAE or AWE with 4GB of ram. You can
verify with just taskmgr. However, the RAM will not be commited until it is
needed. You can also look at the SQL memory manager:target server memory
perfmon counter.
/3GB is ok in this configuration most of the time but I would just use it
when it is needed. Sometimes it causes problem for the OS.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
> Hello,
> We are seeing a differince in size column for the same files between
> sys.database_files and sys.master_files. sys.master_files inidcates
> smaller
> sizes so I think that rules out deferred drop operations. Any ideas?
> this
> is for Tempdb files.
> TIA,
|||Wow,
Super answer makes a lot of sense.
Thank you very much,
Joe
"Tibor Karaszi" wrote:

> Tempdb is special. Sys.master_files holds the size etc to make the tempdb size at startup (remember
> that tempdb is re-created each time you start SQL Server). Sys.database_files inside the database
> holds the actual values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
>
|||TIBOR ROCKS!! :-))
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4A68FA71-9549-4B3F-8F3A-F09D548FF392@.microsoft.com...[vbcol=seagreen]
> Wow,
> Super answer makes a lot of sense.
> Thank you very much,
> Joe
> "Tibor Karaszi" wrote:

Differences in file sizes sys.database_files and sys.master_files

Hello,
We are seeing a differince in size column for the same files between
sys.database_files and sys.master_files. sys.master_files inidcates smaller
sizes so I think that rules out deferred drop operations. Any ideas? this
is for Tempdb files.
TIA,All you need is /3GB. Don't worry about /PAE or AWE with 4GB of ram. You can
verify with just taskmgr. However, the RAM will not be commited until it is
needed. You can also look at the SQL memory manager:target server memory
perfmon counter.
/3GB is ok in this configuration most of the time but I would just use it
when it is needed. Sometimes it causes problem for the OS.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
> Hello,
> We are seeing a differince in size column for the same files between
> sys.database_files and sys.master_files. sys.master_files inidcates
> smaller
> sizes so I think that rules out deferred drop operations. Any ideas?
> this
> is for Tempdb files.
> TIA,|||Tempdb is special. Sys.master_files holds the size etc to make the tempdb size at startup (remember
that tempdb is re-created each time you start SQL Server). Sys.database_files inside the database
holds the actual values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
> Hello,
> We are seeing a differince in size column for the same files between
> sys.database_files and sys.master_files. sys.master_files inidcates smaller
> sizes so I think that rules out deferred drop operations. Any ideas? this
> is for Tempdb files.
> TIA,|||Wow,
Super answer makes a lot of sense.
Thank you very much,
Joe
"Tibor Karaszi" wrote:
> Tempdb is special. Sys.master_files holds the size etc to make the tempdb size at startup (remember
> that tempdb is re-created each time you start SQL Server). Sys.database_files inside the database
> holds the actual values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
> > Hello,
> > We are seeing a differince in size column for the same files between
> > sys.database_files and sys.master_files. sys.master_files inidcates smaller
> > sizes so I think that rules out deferred drop operations. Any ideas? this
> > is for Tempdb files.
> >
> > TIA,
>|||Glad you found it helpful. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4A68FA71-9549-4B3F-8F3A-F09D548FF392@.microsoft.com...
> Wow,
> Super answer makes a lot of sense.
> Thank you very much,
> Joe
> "Tibor Karaszi" wrote:
>> Tempdb is special. Sys.master_files holds the size etc to make the tempdb size at startup
>> (remember
>> that tempdb is re-created each time you start SQL Server). Sys.database_files inside the database
>> holds the actual values.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Joe" <Joe@.discussions.microsoft.com> wrote in message
>> news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
>> > Hello,
>> > We are seeing a differince in size column for the same files between
>> > sys.database_files and sys.master_files. sys.master_files inidcates smaller
>> > sizes so I think that rules out deferred drop operations. Any ideas? this
>> > is for Tempdb files.
>> >
>> > TIA,|||TIBOR ROCKS!! :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4A68FA71-9549-4B3F-8F3A-F09D548FF392@.microsoft.com...
> Wow,
> Super answer makes a lot of sense.
> Thank you very much,
> Joe
> "Tibor Karaszi" wrote:
>> Tempdb is special. Sys.master_files holds the size etc to make the tempdb
>> size at startup (remember
>> that tempdb is re-created each time you start SQL Server).
>> Sys.database_files inside the database
>> holds the actual values.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Joe" <Joe@.discussions.microsoft.com> wrote in message
>> news:94ECFB89-E022-4A97-988D-B1B992B3DE5D@.microsoft.com...
>> > Hello,
>> > We are seeing a differince in size column for the same files between
>> > sys.database_files and sys.master_files. sys.master_files inidcates
>> > smaller
>> > sizes so I think that rules out deferred drop operations. Any ideas?
>> > this
>> > is for Tempdb files.
>> >
>> > TIA,