Differential Vs. Full DB backup

My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon Shargorodsky
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky

Differential Vs. Full DB backup

My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky

Differential Vs. Full DB backup

My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky

Differential Backup...

Why are my differential backups of the same size as full database backups ?
I am just adding few rows to it daily.
Thanks.:rolleyes:I'm having the same problem - any ideas on why my differential size is almost as big as a full?|||I found out that there was optmization job that was running on that database every night, just before the differential backup.

Then I changed the job to run once a week before I take a full database backup, and since then I did not had this problem.

Thanks.

Differential Backup Size and Transaction Log Backup Size !

Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn

Differential Backup File Size

With my maintenance plans for backup jobs, I can control how long the backup
files will remain, hours, days, weeks... before deletion.
With my differential backup jobs, I don't seem to have the ability to
control the residency of a differential backup file to disk, at least I am
not aware of that capability yet, and I am new to SQL Server, and curious as
all get out about its nice features. The differential backup file just
keeps growing and growing and growing. Right now, I manually take the large
file, relocate it to another folder, then when the next differential backup
file is written to the disk location, I then delete the removed large
differential file and continue to let the new backup set grow, repeating the
process as necessary.
Is there a better way to control this process or at least automate it
somehow with T-SQL statements so the large diff file will be transferred to
a new folder until a new file is successfully generated, then deleted. I do
understand the need to maintain the differential file in correspondence with
a current full backup file and corresponding transaction logs with a
corressponding differential file got recovery purpose.
Thanks for your assistance.
Hi,
This is what
You can create two backup jobs for the differential Backups.
Let one run on Mon - Wed - Fri and the other run on Tue - Thu - Sat
On Sunday you can schedule a complete backup.
In Each of these Jobs add one more step that would get executed only if the
preceeding backup step was successful.
Let this new step be of "Operating System Command"
In the Process add the OS command to delete the file from the previous
day's differential backup or even copy the file to a different location.
You can have two separate folders, one each for the Backup Job.
Folder A for Job A and Folder B for job B
When you execute Job A on Monday, let the next step delete/move the backup
file located in the Folder B, after the diff backup is over.
Similarly when you execute Job B on Tuesday, let the next step delete/move
the backup file located in the Folder A, after the diff backup is over.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||That gives a direction to pursue, thank you.
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:i81O3gMMEHA.3364@.cpmsftngxa10.phx.gbl...
> Hi,
> This is what
> You can create two backup jobs for the differential Backups.
> Let one run on Mon - Wed - Fri and the other run on Tue - Thu - Sat
> On Sunday you can schedule a complete backup.
> In Each of these Jobs add one more step that would get executed only if
the
> preceeding backup step was successful.
> Let this new step be of "Operating System Command"
> In the Process add the OS command to delete the file from the previous
> day's differential backup or even copy the file to a different location.
> You can have two separate folders, one each for the Backup Job.
> Folder A for Job A and Folder B for job B
> When you execute Job A on Monday, let the next step delete/move the backup
> file located in the Folder B, after the diff backup is over.
> Similarly when you execute Job B on Tuesday, let the next step delete/move
> the backup file located in the Folder A, after the diff backup is over.
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

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

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >

Different Size Columns in Different Table Rows?

I'm trying to recreate something that I could do in Crystal Reports and have
hit a wall with Report Designer.
I want to create two (or more) header rows and two (or more) detail rows
with each column width different from the column width from the row above
(or below). I found how to add more header and detail rows but the column
widths remain the same for each row and cannot be adjusted separately. What
I'd like to to is to "merge" table columns for one report row into just one
column with the other row remaining as two columns.
For example, I want a report where the first row of detail contains multiple
columns that contains information about a database column, but then the next
detail row should only contain ONE column.
Type of Note Date of Note Author of Note
Note Text
---
Addendum 10/17/07 Shakespeare
This is the text of the note of an addendum written by Shakespeare
Is there a way to do this with RS, or is there some workaround?
Thanks for any help.Never mind (again) ;) I found the Merge Cells right-click command.
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23HQrXMNEIHA.4752@.TK2MSFTNGP04.phx.gbl...
> I'm trying to recreate something that I could do in Crystal Reports and
> have hit a wall with Report Designer.
> I want to create two (or more) header rows and two (or more) detail rows
> with each column width different from the column width from the row above
> (or below). I found how to add more header and detail rows but the column
> widths remain the same for each row and cannot be adjusted separately.
> What I'd like to to is to "merge" table columns for one report row into
> just one column with the other row remaining as two columns.
> For example, I want a report where the first row of detail contains
> multiple columns that contains information about a database column, but
> then the next detail row should only contain ONE column.
> Type of Note Date of Note Author of Note
> Note Text
> ---
> Addendum 10/17/07 Shakespeare
> This is the text of the note of an addendum written by Shakespeare
> Is there a way to do this with RS, or is there some workaround?
> Thanks for any help.
>

Different footer size for first page

Hi,
I am trying to create invoices where first page should have ~3.75in footer
with remittance slip. All other pages should have small footer.
Body of the report is one big table with groups.
What is the [best] way to accomplish this? Footer size could not be
formula-based (at least though VS2K5).
I am useing RS2K5SP1.
Thanks
-AndreyHi Andrey,
Thanks for using MSDN Managed Newsgroup Support.
Footer size can not use expressions.
I would like to know if it's possible for you to add your remittance slip
in the firs page content.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Different footer size for first page
>thread-index: AcZqNrS4PipuEn7hSOe3RyettPbMqw==>X-WBNR-Posting-Host: 205.143.175.124
>From: =?Utf-8?B?QW5kcmV5?= <AndreyN@.newsgroups.nospam>
>Subject: Different footer size for first page
>Date: Thu, 27 Apr 2006 13:11:01 -0700
>Lines: 15
>Message-ID: <89E7F9E4-A6DE-47FF-A7CA-A01F81E95B34@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:72865
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi,
>I am trying to create invoices where first page should have ~3.75in footer
>with remittance slip. All other pages should have small footer.
>Body of the report is one big table with groups.
>What is the [best] way to accomplish this? Footer size could not be
>formula-based (at least though VS2K5).
>I am useing RS2K5SP1.
>Thanks
>-Andrey
>|||Wei,
Report itself is a table with multiple groups which can span multiple pages.
How can I add a slip to the end of first page other than putting it into the
footer?
Is there any way to have deferent footers for first page and all other pages
(similar to Word)?
Thanks
-Andrey
"Wei Lu" wrote:
> Hi Andrey,
> Thanks for using MSDN Managed Newsgroup Support.
> Footer size can not use expressions.
> I would like to know if it's possible for you to add your remittance slip
> in the firs page content.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Different footer size for first page
> >thread-index: AcZqNrS4PipuEn7hSOe3RyettPbMqw==> >X-WBNR-Posting-Host: 205.143.175.124
> >From: =?Utf-8?B?QW5kcmV5?= <AndreyN@.newsgroups.nospam>
> >Subject: Different footer size for first page
> >Date: Thu, 27 Apr 2006 13:11:01 -0700
> >Lines: 15
> >Message-ID: <89E7F9E4-A6DE-47FF-A7CA-A01F81E95B34@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >Path: TK2MSFTNGXA01.phx.gbl
> >Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:72865
> >NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >Hi,
> >
> >I am trying to create invoices where first page should have ~3.75in footer
> >with remittance slip. All other pages should have small footer.
> >
> >Body of the report is one big table with groups.
> >
> >What is the [best] way to accomplish this? Footer size could not be
> >formula-based (at least though VS2K5).
> >
> >I am useing RS2K5SP1.
> >
> >Thanks
> >
> >-Andrey
> >
>|||Hi Andrey,
The reporting services pagination is different with Word. The pagination in
the Reporting Service can not use the formula expression.
As you are using the group to add the page breaks, I think you need to
design a subreport and include only the first page content and the slip.
Then, you could put the other data in the report and with the different
page footer.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Different footer size for first page
>thread-index: AcZqhlU29tf9nijmTwuP3TUJOP/8+Q==>X-WBNR-Posting-Host: 68.122.5.247
>From: =?Utf-8?B?QW5kcmV5IE5pa2lmb3Jvdg==?= <AndreyN@.newsgroups.nospam>
>References: <89E7F9E4-A6DE-47FF-A7CA-A01F81E95B34@.microsoft.com>
<6P8i0XnaGHA.1244@.TK2MSFTNGXA01.phx.gbl>
>Subject: RE: Different footer size for first page
>Date: Thu, 27 Apr 2006 22:41:01 -0700
>Lines: 77
>Message-ID: <0A370993-DE0E-409E-B6AB-E73193657815@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:72881
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Wei,
>Report itself is a table with multiple groups which can span multiple
pages.
>How can I add a slip to the end of first page other than putting it into
the
>footer?
>Is there any way to have deferent footers for first page and all other
pages
>(similar to Word)?
>Thanks
>-Andrey
>"Wei Lu" wrote:
>> Hi Andrey,
>> Thanks for using MSDN Managed Newsgroup Support.
>> Footer size can not use expressions.
>> I would like to know if it's possible for you to add your remittance
slip
>> in the firs page content.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> --
>> >Thread-Topic: Different footer size for first page
>> >thread-index: AcZqNrS4PipuEn7hSOe3RyettPbMqw==>> >X-WBNR-Posting-Host: 205.143.175.124
>> >From: =?Utf-8?B?QW5kcmV5?= <AndreyN@.newsgroups.nospam>
>> >Subject: Different footer size for first page
>> >Date: Thu, 27 Apr 2006 13:11:01 -0700
>> >Lines: 15
>> >Message-ID: <89E7F9E4-A6DE-47FF-A7CA-A01F81E95B34@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >Path: TK2MSFTNGXA01.phx.gbl
>> >Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.reportingsvcs:72865
>> >NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> >
>> >Hi,
>> >
>> >I am trying to create invoices where first page should have ~3.75in
footer
>> >with remittance slip. All other pages should have small footer.
>> >
>> >Body of the report is one big table with groups.
>> >
>> >What is the [best] way to accomplish this? Footer size could not be
>> >formula-based (at least though VS2K5).
>> >
>> >I am useing RS2K5SP1.
>> >
>> >Thanks
>> >
>> >-Andrey
>> >
>>
>

Different Font size when browsing and printing a report ?

Hi all,

Is it possible to setup different font size when a user is browsing or printing a report?

I have a lot of data to print on a report and i need tu use a font size of 7pt which is fine for printing but when the user is looking at the report on the browser a font size of 7pt is a bit too small and the bold doesn't work.

Tia

You can use a parameter (Web, Print) which would specify the font size 7, 12, and set the font property on the textbox to use the =Parameters!FontSize.Value + 'pt' expression.

I don't think the report can tell whether it's being printed or not internally.

cheers,

Andrwe

|||

Thanks Andrew,

I was hoping to find a way to do this without prompting the user beforehand.

Regards,

Eric

|||I am trying to implement the same thing , where i can make font size smaller than 8pt but show as font weight bold. any help will be appriciated.

Different font size and style in the same table cell

I have a table cell that contains two things:

well code and well type (two different data fields concatenated)

for example:

4WA53102 Verticall Well

I want the code to be 12 pt arial regular

I want the well type to be 8 point italic arial

4WA53102 Vertical Well

How do I do this? Thanks

Why not put well code and well type in adjacent table cells and then you can change the formatting on the cell well code is in and the formatting on the cell well type is in to be different from each other. I don't see how it would be different from what you want to do.

It isn't possible to do what you want the way you want to do it.

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

Differences in varchar size usage, declaring the maximum available of 8000 characters

Hi, I actually found a thread on this subject and post a follow-up question to it, but it seems that nobody is viewing the thread, probably because it is marked as answer, as such, I post a new thread for my question.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87635&SiteID=1

My question is mainly on the differences between using a varchar(500) compared to varchar(8000), in the event when the stored string is less than 500 characters.

From the explanations that I see from the thread, can I say that to declare a column with a size of 8000 will not have any difference in storage/performance when compared to a column declared with a size of 500, when the stored string in the column is of 100 characters? The drawback of having a larger size declaration is that the probability that a string of 8000 characters might be stored in it in the future (either accidentally or breaking a non desire rule)? So the problem is not a technical one but rather on the soft side where we left a loose control whereby we leave a chance that it might cause a technical issue in the future (worst case)?

Apart from stored data in table, how about a declaration of varchar(8000) in a stored procedure variable or parameter?

Thanks

Eugene

The storage of varchar is always the same. We store the length of the actual data, and the data itself. The length in the table definition is used to validate that the varchar is not longer than the declared length, but does not take up any storage.

The same argument is valid when you use variables as well.

My suggestion would be that you define the size of the variable or column to what makes sense to your application. If you never need more than 500 chars, I don't see the need to define a table as varchar(8000).

Thanks,

Marcel van der Holst
[MSFT]|||

Hi Marcel,

Thanks for the answer, will help me much in designing table. So, about the variables, from your answer, you mean the system would not allocate enough space in memory according to the declared size? What I mean is, the storage space in memory will keep growing as the string length changes, or maybe it is actually creating a new string altogether in a new memory space; rather than allocating a large enough space earlier?

In this case, if the memory space is kept enlarging, or allocating a new space, due to some string intensive operation in the defined procedure/function, would it affect the performance, as the developer can't get to define the maximum probable size, which he knows/can assume?

thanks a million

Eugene

|||

SQL Server does not really allocate space based on the datatype definition in your table. It allocates enough space to hold the data (so if you have 10 chars, it only allocates space for 10 chars). After the data is in memory, one of the checks we do is check the length of the data against the defined length. If the size of the data is bigger, we raise an error. Internally, SQL Server uses several algorithms to avoid too many memory allocations (sometimes it allocates more than needed if it finds out that a lot of string concatenation is being done). The defined size of a variable does not really play a big role here.

Are you using SQL Server 2000 or SQL Server 2005?

If you use SQL Server 2005, you can use the varchar(max) datatype, which will hold data up to 2GB, and you never really have to worry about the size. SQL Server will do the right thing for you. Only when your data gets bigger than 8K, you might get a small perf hit when using varchar(max) as it won't fit on a single page anymore, but if you have a lot of data that is smaller than 8K, you might as well use varchar(max) as internally in the engine varchar(N) and varchar(max) are treated very simular as long as the data is less than 8K.

Hope this helps,

Marcel van der Holst

[MSFT]

|||

Cool work there, I got you. For the table, I think it's pretty straightforward where it is only storing the exact string (length) with some overhead. So for variable/parameter in memory, the memory space is actually a dynamic one (where it can grow or something else), but SQL Server is smart enough to allocate a size that should optimize the situation.

I guess my understanding should be correct, right?

I use both SQL Server 2000 and SQL Server 2005.

Thanks, it helps.

Eugene

|||Yes, your understanding is correct.

Thanks,

Marcel van der Holst
[MSFT]

Differences in varchar size usage, declaring the maximum available of 8000 characters

Hi, I actually found a thread on this subject and post a follow-up question to it, but it seems that nobody is viewing the thread, probably because it is marked as answer, as such, I post a new thread for my question.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87635&SiteID=1

My question is mainly on the differences between using a varchar(500) compared to varchar(8000), in the event when the stored string is less than 500 characters.

From the explanations that I see from the thread, can I say that to declare a column with a size of 8000 will not have any difference in storage/performance when compared to a column declared with a size of 500, when the stored string in the column is of 100 characters? The drawback of having a larger size declaration is that the probability that a string of 8000 characters might be stored in it in the future (either accidentally or breaking a non desire rule)? So the problem is not a technical one but rather on the soft side where we left a loose control whereby we leave a chance that it might cause a technical issue in the future (worst case)?

Apart from stored data in table, how about a declaration of varchar(8000) in a stored procedure variable or parameter?

Thanks

Eugene

The storage of varchar is always the same. We store the length of the actual data, and the data itself. The length in the table definition is used to validate that the varchar is not longer than the declared length, but does not take up any storage.

The same argument is valid when you use variables as well.

My suggestion would be that you define the size of the variable or column to what makes sense to your application. If you never need more than 500 chars, I don't see the need to define a table as varchar(8000).

Thanks,

Marcel van der Holst
[MSFT]|||

Hi Marcel,

Thanks for the answer, will help me much in designing table. So, about the variables, from your answer, you mean the system would not allocate enough space in memory according to the declared size? What I mean is, the storage space in memory will keep growing as the string length changes, or maybe it is actually creating a new string altogether in a new memory space; rather than allocating a large enough space earlier?

In this case, if the memory space is kept enlarging, or allocating a new space, due to some string intensive operation in the defined procedure/function, would it affect the performance, as the developer can't get to define the maximum probable size, which he knows/can assume?

thanks a million

Eugene

|||

SQL Server does not really allocate space based on the datatype definition in your table. It allocates enough space to hold the data (so if you have 10 chars, it only allocates space for 10 chars). After the data is in memory, one of the checks we do is check the length of the data against the defined length. If the size of the data is bigger, we raise an error. Internally, SQL Server uses several algorithms to avoid too many memory allocations (sometimes it allocates more than needed if it finds out that a lot of string concatenation is being done). The defined size of a variable does not really play a big role here.

Are you using SQL Server 2000 or SQL Server 2005?

If you use SQL Server 2005, you can use the varchar(max) datatype, which will hold data up to 2GB, and you never really have to worry about the size. SQL Server will do the right thing for you. Only when your data gets bigger than 8K, you might get a small perf hit when using varchar(max) as it won't fit on a single page anymore, but if you have a lot of data that is smaller than 8K, you might as well use varchar(max) as internally in the engine varchar(N) and varchar(max) are treated very simular as long as the data is less than 8K.

Hope this helps,

Marcel van der Holst

[MSFT]

|||

Cool work there, I got you. For the table, I think it's pretty straightforward where it is only storing the exact string (length) with some overhead. So for variable/parameter in memory, the memory space is actually a dynamic one (where it can grow or something else), but SQL Server is smart enough to allocate a size that should optimize the situation.

I guess my understanding should be correct, right?

I use both SQL Server 2000 and SQL Server 2005.

Thanks, it helps.

Eugene

|||Yes, your understanding is correct.

Thanks,

Marcel van der Holst
[MSFT]

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,