Reducing Backup Size

Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
--
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.

Reducing Backup Size

Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to b
e
very cost productive. But Lightspeed will proably be as close to an industr
y
standard that you will get.
--
"C TO" wrote:

> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right aft
er
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.

Reducing 5 values to 1 value

I am trying to take values that exist in a single column and create a column
elsewhere that contains comma separated values of that dataset.
The '5' is arbitrary...
For example,
Greg
John
Bill
Carol
Judy
Julie
Would look like
Greg, John, Bill, Carol, Judy, Julie
Appreciate in advance any thoughts....
GregA way, maybe not the best way
DECLARE @.name varchar(512)
DECLARE @.list varchar(512)
SET @.name = ''
SET @.list = ''
DECLARE names_cursor STATIC FORWARD_ONLY
FOR
SELECT DISTINCT [name] FROM [YourTable]
OPEN names_cursor
FETCH NEXT FROM names_cursor INTO @.name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.list = @.list + @.name
FETCH NEXT FROM names_cursor INTO @.name
END
INSERT INTO [OtherTable] VALUES (@.list)
CLOSE names_cursor
DEALLOCATE names_cursor
Look at
http://www.microsoft.com/technet/co...r />
931d-e02a
cbb88505
for a trigger that I wrote that would keep those two tables syncrhonized.
"Greg" wrote:

> I am trying to take values that exist in a single column and create a colu
mn
> elsewhere that contains comma separated values of that dataset.
> The '5' is arbitrary...
> For example,
> Greg
> John
> Bill
> Carol
> Judy
> Julie
> Would look like
> Greg, John, Bill, Carol, Judy, Julie
> Appreciate in advance any thoughts....
> Greg
>|||Ahh I asked this one the other day...
See http://www.aspfaq.com/show.asp?id=2529
Or see this post..
http://groups.google.co.uk/group/mi...7f3f3d7de2dd4fa
Barry

Reduceing the filesize of datafiles

Hi,
in the enterprise manger I want to reduce the datafiles of a database. When
I do this the enterprise manager says,
that there are 80% (more then 17GB), that can be freed, but after the cleani
ng process the datafiles would not be
shorter then before.
How can I clean the freeable memory of the datafiles ?
Is there an other way that works better ?
Best regards
Axel LanserAlex
Look at DBCC SHRINKDATABASE or DBCC SHRINKFILE in the BOL.
"Axel Lanser" <anonymous@.discussions.microsoft.com> wrote in message
news:EDD50E53-D546-4F24-90EB-674C0172361C@.microsoft.com...
> Hi,
> in the enterprise manger I want to reduce the datafiles of a database.
When I do this the enterprise manager says,
> that there are 80% (more then 17GB), that can be freed, but after the
cleaning process the datafiles would not be
> shorter then before.
> How can I clean the freeable memory of the datafiles ?
> Is there an other way that works better ?
> Best regards
> Axel Lanser|||Hi,
Did you meant "How to reduce the datafile size", If yes then execute the
below command in query analyzer,
DBCC SHRINKFILE('logicalfilename','truncateon
ly')
If it is TX log file, perform a 'Backup LOG' before shrinking the file.
Refer the below link for more info.
http://www.sql-server-performance.c...p?TOPIC_ID=3140
Thanks
Hari
"Axel Lanser" <anonymous@.discussions.microsoft.com> wrote in message
news:EDD50E53-D546-4F24-90EB-674C0172361C@.microsoft.com...
> Hi,
> in the enterprise manger I want to reduce the datafiles of a database.
When I do this the enterprise manager says,
> that there are 80% (more then 17GB), that can be freed, but after the
cleaning process the datafiles would not be
> shorter then before.
> How can I clean the freeable memory of the datafiles ?
> Is there an other way that works better ?
> Best regards
> Axel Lanser|||Hi,
I have made a 'backup log' before I start the dbcc shrinkfile for the Log-Fi
le, but the Log-File wouldn't be reduce.
I think all active transaction must be proccessed, because the data base is
in single user mode and I was permanently
conected with this database and without any new transaction on this database
for two days.
Therfore I don't know why the Logfile is not reduceable.
Can you help me ?
What can I do ?
Thanks
Axel|||http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Axel Lanser" <anonymous@.discussions.microsoft.com> wrote in message
news:B15B5790-C11F-4D51-8D0E-64FA48EB0B48@.microsoft.com...
> Hi,
> I have made a 'backup log' before I start the dbcc shrinkfile for the
Log-File, but the Log-File wouldn't be reduce.
> I think all active transaction must be proccessed, because the data base
is in single user mode and I was permanently
> conected with this database and without any new transaction on this
database for two days.
> Therfore I don't know why the Logfile is not reduceable.
> Can you help me ?
> What can I do ?
> Thanks
> Axel

reduced functionality with Web Services

I'm curious exactly what functionality is lost when you use the RS Web
Service rather than accessing the direct URL. We're evaluating RS vs Crystal
and specifically need XLS export functionality. Is this lost if you use the
RS Web Service? Is there a way to export to PDF, XLS, etc. and still use the
RS Web Service?
Regards,
David GardnerIf you render using the web service, you loose the nice html toolbar you
get with URL access:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_0pr6.asp
You can pass a format string to the Render method of the web service. It
supports multiple formats (including Excel, PDF...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_6x0z.asp
Make sure you have Reporting Services SP1 installed, as there were a number
of fixes to the rendering extensions included in that service pack.
--
Adrian M.
MCP
"David Gardner" <David.Gardner-Logan@.REMOVECAPSrrd.com> wrote in message
news:6CD498D5-ED27-4626-89CF-49DC8E61DAB6@.microsoft.com...
> I'm curious exactly what functionality is lost when you use the RS Web
> Service rather than accessing the direct URL. We're evaluating RS vs
> Crystal
> and specifically need XLS export functionality. Is this lost if you use
> the
> RS Web Service? Is there a way to export to PDF, XLS, etc. and still use
> the
> RS Web Service?
> Regards,
> David Gardner

Reduce view state size

We are having problems with SQL 200 Reporting services with large view states
being passed to the browser (up to 20Mb). Is there any way of reducing the
view state size used by reporting services, or any way of storing view state
in a database rather than having it passed to the browser with every request?
Thanks,
SJHHi SJH,
I would like to know does this issue appeared on a specified report? If so,
what does this report contained? Does it contain any large amount of text?
It's weird that your reporting services using so much ViewState. How did
you figure out this behavior? Have you got any error message?
Please let me know the information so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I've tried to checked on the exact circumstances for this problem, but can't
speak to the individual who reported the problem today.
The problem seems to occur when creating a new subscription for a report.
It does not happen on all reports. I'm not yet sure if it is isolated to a
single report.
S
"Wei Lu [MSFT]" wrote:
> Hi SJH,
> I would like to know does this issue appeared on a specified report? If so,
> what does this report contained? Does it contain any large amount of text?
> It's weird that your reporting services using so much ViewState. How did
> you figure out this behavior? Have you got any error message?
> Please let me know the information so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hi SJH,
Is the subscription a Data driven subscription or a regular subscription?
Have you got any error message when you create the subscription? What's the
report when you try to create the subscription?
Please let me know the result so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi SJH,
How is everything going? Please let me know if you have any questions or
concerns. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support

reduce trans log size; help needed

Hello,

I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.

As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!

If you know of a well-documented tutorial (Aside from Books Online (F1)),
please point me in the right direction.

Regards.First, create a backup device with whatever name you want. ( I assume you
know how to do this).
Might have to check the syntax on each of these but here goes

1. backup database [your_database_name] to [your_backup_device] with stats,
init
2. backup tran [your_database_name] to [your_backup_device]
3. dbcc shrinkfile 2,truncateonly
4. backup database [your_database_name] to [your_backup_device]

Step 3 above assumes that file 2 is the logfile for your database. You
might have to check this by issuing a select * from sysfiles (in query
analyzer) to make sure that file 2 is the log file.

Hope this helps.

Oscar...

> 1.back-up the entire database
> 2.truncate the log
> 3.shrink the log
> 4.back-up once again.
> As you may have determined, I am relatively new to managing a sql server
> database and while I have found multiple articles online about the topics
I
> need to accomplish, I cannot find any actual examples that explain where I
> input the coded used to accomplish the above-mentioned steps. I do
> understand the theory behind the steps I just do not know how to
accomplish
> them!