compression for log shipping

Hi All
I am using the Simple Log Shipping in the SQL resource kit.
just wondering if there is any way I can compress the log during the log
shipping processing?
thanks
Justin
Nope. SQL Server does not support compressing backups. Why? I have
absolutely no idea at all. Want it in the product? Get a few thousand of
your friends to make it an issue to add the feature.
http://lab.msdn.microsoft.com/produc...k/Default.aspx
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Justin" <justin@.innocity.net> wrote in message
news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am using the Simple Log Shipping in the SQL resource kit.
> just wondering if there is any way I can compress the log during the log
> shipping processing?
> thanks
> Justin
|||As Michael mentioned, the is no compressing support in the Sql2005 release.
This feature has been considered for the future release though.
Thanks
Yunwen
Disclaimer: This posting is provided “AS IS” with no warranties, and confers
no rights. You assume all risk for your use.
"Michael Hotek" wrote:

> Nope. SQL Server does not support compressing backups. Why? I have
> absolutely no idea at all. Want it in the product? Get a few thousand of
> your friends to make it an issue to add the feature.
> http://lab.msdn.microsoft.com/produc...k/Default.aspx
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Justin" <justin@.innocity.net> wrote in message
> news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
>
>

compression and the Primary XML index

Is SS08 compression going to help me with my XML. The Primary index is really just a table it appears, so I was hoping to compress that ... plesae?

We are currently doing tests to see if compression will be useful for XML indexes (both from savings point of view and performance point of view). Based on the results, and whether we have resources available or not, support might be added.

It is definitely one of the areas that we are considering.

Thanks,

Compression

Hello,

I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?

Thanks for all the help.DUMP

DBCC SHRINKFILE|||Originally posted by Brett Kaiser
DUMP

DBCC SHRINKFILE

How would you do a shrink file?|||Do you have access to Books Online?

Go to the U=Index and look it up

Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

Look up DBBC SHRINKDATABASE as well|||Originally posted by Brett Kaiser
Do you have access to Books Online?

Go to the U=Index and look it up

Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

Look up DBBC SHRINKDATABASE as well

Thanks again for all your help.|||Shrinkdatabase May not work All the time . Try using Shrinkfiles too.

Also, it is not a good practice to leave Shrink_db option checked ..

Compressing SQL Server Database

Newbie - SQL 2000. I have a database file 150 MB and my log file is 400 MB. Is there anyway I can compress the size of these files.
Thanks,If you don't need to take the backup of the Transaction Log:::

Take the full database backup(Recommended not Reqd)

go to Query Analyzer and
use [your database]
backup TRAN [your dbname] with no_log

once it's over then you can run:

dbcc SHRINKFILE([YOUR DBNAME_LOGFILENAME,TRUNCATEONLY)

and it will reduce the size of the Transaction Log

Compressed Snapshot Files!

Hello All,
I'm using Transactional Replication. I have compress snapshot file now I
want to configure it on Subscriber.
I saw BOL and it says,
1. In Microsoft SQL Server Enterprise Manager, expand the subscription
database and the Subscriptions directory, right- click a subscription,
and then click Properties.
but didnt find the Subscription directory under subscription database.
How can I configure?
Thanks in advance.
Naveed.
the snapshot files will be compressed in the cab format in your alternate
snapshot folder. If you are doing a pull subscription you have the option to
applying the snapshot (compressed or uncompressed) from an alternate
location by using the "Use snapshot files from the following folder option."
You will have to prove a path to the root of the snapshot location, ie
\\PublisherServerName\Share\ where this will map to c:\temp or wherever you
place your files in. If you copy these files locally you have to copy the
directory structure from unc on, ie if you place the files in c:\temp you
would copy the contents of unc to c:\temp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I'm using Transactional Replication. I have compress snapshot file now I
> want to configure it on Subscriber.
> I saw BOL and it says,
> 1. In Microsoft SQL Server Enterprise Manager, expand the subscription
> database and the Subscriptions directory, right- click a
subscription,
> and then click Properties.
> but didnt find the Subscription directory under subscription database.
> How can I configure?
> Thanks in advance.
> Naveed.
>
|||Hello Hilary!!!
Thanks for your reply, You gave solution for Pull Subscription But We are
using Push Subscription and we have very slow connection for publishing data
on subscriber, i.e why we compress snapshot file in alternate location and
now we want to configure it on subscriber.
How to do that?
Thanks,
Naveed.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> the snapshot files will be compressed in the cab format in your alternate
> snapshot folder. If you are doing a pull subscription you have the option
to
> applying the snapshot (compressed or uncompressed) from an alternate
> location by using the "Use snapshot files from the following folder
option."
> You will have to prove a path to the root of the snapshot location, ie
> \\PublisherServerName\Share\ where this will map to c:\temp or wherever
you
> place your files in. If you copy these files locally you have to copy the
> directory structure from unc on, ie if you place the files in c:\temp you
> would copy the contents of unc to c:\temp.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Naveed" <nrehman@.marsonssoft.com> wrote in message
> news:OoosGusqEHA.2732@.TK2MSFTNGP09.phx.gbl...
> subscription,
>
|||use the -AltSnapshotFolder switch on your distribution agent. make sure you
uncheck the generate snapshot in the default folder location for your
snapshot properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:eUFealtqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Hello Hilary!!!
> Thanks for your reply, You gave solution for Pull Subscription But We are
> using Push Subscription and we have very slow connection for publishing
data[vbcol=seagreen]
> on subscriber, i.e why we compress snapshot file in alternate location and
> now we want to configure it on subscriber.
> How to do that?
> Thanks,
> Naveed.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eQGmKXtqEHA.3416@.TK2MSFTNGP15.phx.gbl...
alternate[vbcol=seagreen]
option[vbcol=seagreen]
> to
> option."
> you
the[vbcol=seagreen]
you[vbcol=seagreen]
I[vbcol=seagreen]
subscription
>
sqlsql

Compress/Zip Reporting Services Export options

Hey everyone,

I have an issue where i am sending out files with 30,000+ lines and they are reaching the 11mb, 12mb in size.

This is becoming and issue for us, as we are only allowed to email up to 10mb in size.

I have tried reducing all spaces in the data, removing any graphics etc from the report , but still the excel file is over 11mb. One thing i did find was that, if i export it to excel, then open the file and save as a different file name the file size drops 50% !!

I was wondering if anyone has been able to zip/compress the exported file before it gets emailed?

It would be a great feature for MS to include in the next service pack.. Take advantage of the built in Zip support in Windows..

Look forward to hearing any suggestions that the community may have,

Thanks

Scotty

Hi there-

Unfortunately you'll have to create a custom delivery extension for your report server. You can look at the PrinterDeleiveryExample in the RS samples folder (located wherever you installed RS) to give you an idea of what you'll need to do.

If you are using .Net 2.0, then you can use the System.IO.Compression and System.Net.Mail libraries as part of your delivery extension. I unfortunately have a client that hasn't migrated to .Net 2.0 and I had to use .Net 1.1. I used two open source libraries to accomplish the task: DotNetOpenMail and SharpZipLib. I decided to use DotNetOpenMail, because you can create an attachment from an array of bytes, which you can't do with the System.Web.Mail library in .Net 1.1. So for my custom delivery extension, I zipped my report to a memory stream and then attached that stream's byte array to the e-mail.

Here are some links to get you started:
DotNetOpenMail - http://dotnetopenmail.sourceforge.net
SharpZipLib - http://www.icsharpcode.net/OpenSource/SharpZipLib

Hope that gives you a starting point to a solution. If you have any questions, please respond back here.

Regards,
Scott

Compress text column on SQL2000

I am trying to compress text/image column on a table on MSSQL200 Enterprise Ed.
When will compress when write to the table and uncompress when user retrieve
the data. Anyone can please suggest me how to or any tool to do this.
You need to do the compression and uncompression in the client app or middle
tier and not the database server.
Andrew J. Kelly SQL MVP
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
>I am trying to compress text/image column on a table on MSSQL200 Enterprise
>Ed.
> When will compress when write to the table and uncompress when user
> retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||To add to Andrew's response, I recommend that you test very heavily before
implementing this in a production environment; I've done fairly extensive
testing of various on-the-fly .NET compression libraries for the purpose of
compressing LOB data on the way in and out of the database, and found that
instead of improving performance as I expected (due to lowering disk IOs and
network bandwidth required to retrieve the data), there was instead a
moderate degredation due to the extra processor strain on the middle tier.
If you do find a way to improve performance using compression, I would be
very interested in hearing about your techniques and results -- of course,
if you're doing this only for disk space savings, you can disregard my
rantings...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> I am trying to compress text/image column on a table on MSSQL200
Enterprise Ed.
> When will compress when write to the table and uncompress when user
retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||Andrew J. Kelly wrote:
> You need to do the compression and uncompression in the client app or
> middle tier and not the database server.
>
The OP could place the TEXTIMAGE on a filegroup that is located on a
compressed folder on the server. That's supported by SQL Server if I'm
not mistaken and won't require any additional libraries to manage.
Andrew, any thoughts?
David Gugick
Imceda Software
www.imceda.com
|||Actually I know using compressed volumes is not recommended and I think it
may even be unsupported for Sql Server.
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23S5%23N4uGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
> The OP could place the TEXTIMAGE on a filegroup that is located on a
> compressed folder on the server. That's supported by SQL Server if I'm not
> mistaken and won't require any additional libraries to manage.
> Andrew, any thoughts?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Andrew J. Kelly wrote:
> Actually I know using compressed volumes is not recommended and I
> think it may even be unsupported for Sql Server.
>
You are correct:
http://support.microsoft.com/kb/231347/EN-US/
David Gugick
Imceda Software
www.imceda.com
|||What about upgrade to use Yukon, use CLR written in c# to write unpresss text
and store on sqlserver? Anyone has done this sort of thing?
"Adam Machanic" wrote:

> To add to Andrew's response, I recommend that you test very heavily before
> implementing this in a production environment; I've done fairly extensive
> testing of various on-the-fly .NET compression libraries for the purpose of
> compressing LOB data on the way in and out of the database, and found that
> instead of improving performance as I expected (due to lowering disk IOs and
> network bandwidth required to retrieve the data), there was instead a
> moderate degredation due to the extra processor strain on the middle tier.
> If you do find a way to improve performance using compression, I would be
> very interested in hearing about your techniques and results -- of course,
> if you're doing this only for disk space savings, you can disregard my
> rantings...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
> news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> Enterprise Ed.
> retrieve
>
>
|||"SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> What about upgrade to use Yukon, use CLR written in c# to write unpresss
text
> and store on sqlserver? Anyone has done this sort of thing?
I haven't tried yet -- but I plan to when the next beta is released and
performance is improved a bit. MS hadn't started performance tuning the
last releases much so it wasn't worth testing, IMO.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Please let us know later the.
"Adam Machanic" wrote:

> "SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
> news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> text
> I haven't tried yet -- but I plan to when the next beta is released and
> performance is improved a bit. MS hadn't started performance tuning the
> last releases much so it wasn't worth testing, IMO.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

Compress SQL Server Backup File.

I have a Windows 2003 Server, what my plan is to compress sql server backup
file every night and ship in to different SQL Server Server, the question
is, is there any builtin zip/compress facility in windows which I can use it
for this purpose.
Thanks
If you are using the NTFS file system, you can just configure a directory to
be compressed in the advanced properties but when you read the file to send
it to the backup server, Windows will uncompress it so if you want the data
to be compressed on the wire, you will probably need to use some kind of zip
utility. Depending on how big the files are, compressing on the wire might
not make that big a difference so it might be enough to make the backup
directories compressed. Be sure you don't accidentally compress the
database files - this will break SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Rogers" <naissani@.hotmail.com> wrote in message
news:eZves4UPGHA.1312@.TK2MSFTNGP09.phx.gbl...
>I have a Windows 2003 Server, what my plan is to compress sql server backup
>file every night and ship in to different SQL Server Server, the question
>is, is there any builtin zip/compress facility in windows which I can use
>it for this purpose.
> Thanks
>
|||Rogers wrote:
> I have a Windows 2003 Server, what my plan is to compress sql server
> backup file every night and ship in to different SQL Server Server,
> the question is, is there any builtin zip/compress facility in
> windows which I can use it for this purpose.
> Thanks
I would recommend you consider using a SQL Server backup and recovery
program that compresses and optionally encrypts backups in memory.
You'll save yourself a lot of backup (and recovery) time and won't have
to worry about post-backup compression. There are a number of companies
who have this type of software.
David Gugick - SQL Server MVP
Quest Software
|||Hi,
of course there are several tools in the market making a good job, but
sometimes you are really interested in a subset of their functionality
or your budget is really reduced. Four these cases, I would suggest
take a look here
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart =blogview&_c=blogpart#permalink
Regards

compress spaces

i have this textboxes that have data in it and its possible that the textbox has no data. so what i did was just hide it but the spaces are still visible. the size of my report is just 5 X 11, it is vertical in form.so how can i compress the spaces. pls help thanks!

Hi Maila,

one gud approach is to make the widht of the textbox to 0 and set the can grow property to true. so that if the textbox contains data it would expand horizontally.

Cheers

Chakri.

|||ill try it thanks a lot!
sqlsql

Compress or Compact Database with SQL Express

How do you Compress or Compact a Database with SQL Express?

If feedback is of benefit, this would be a useful feature to include with SSME.

Regards,

FlavelleRight click on the database in Object Explorer, select Tasks > Shrink > Database.|||Thanks - that was too easy!

Regards,

Flavelle

Compress MSDE DB

Is there a way to run a compress using the command line?
If so what is the command?
What do you mean by compress the MSDE ? If you want to try to shrink the
logfile / the datafiles (releasing unsused space int the files to the
operating system) you could try executing the commadn to compress these
files via osql.exe
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Jon" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:175901c54f32$3e0b1540$a601280a@.phx.gbl...
> Is there a way to run a compress using the command line?
> If so what is the command?

Compress full backup file

Hello,

Is it possible to compress a full backup file? I've noticed that the backup file size is usually the same size of the working database.
My goal is to compress and break in small parts the backup file to be send to another location.

Does anyone knows a program to do that?

Thanks for the help!
Diogo SantosRefer to http://www.sqllitespeed.com/ (SQL Litespeed) for more information which is lot quicker and reliable.

Compress Db

Hi ,
My database in SQL server keep on increasing daily
after trasactional data keeps on updates to the database
and the database size now already more than 10 gigabytes.
1) How can I compress this database to make the size
smaller ?
2) What is the advisible way to maintain such huge
database ?
3) Beside indexing the tables , any other way to make the
database perform well ?
Please guide me.
TQ
Hi
Have you try DBCC SHRINKFILE or DBCC SHRINKDATABASE?
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>
|||Hi,
Can you identify the usage of Data and Log first using the command
sp_helpdb <dbname>
see the value of the column size, if the trasnaction log size is big then
use the below command to identify the exact usage.
dbcc sqlperf(logspace)
If the size of the log is huge and if the utilization percentage is very
less then you can shrink the transaction based on the URI's post.
steps:-
1. Perform a transaction log backup (backup log command)
2. DBCC SHRINKFILE('logical_ldf_name','truncateonly')
Now the LDF file will be shrinked. verify using dbcc sqlperf(logspace)
Note:
You can also check the data and index utlization using:- If you have a small
value do not shrink the MDF file.
use <dbname>
go
sp_spaceused
If the unused value is very high , then you can shrink the mdf file as well.
Thanks
Hari
MCDBA
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>
|||If the log is the file which is huge, then either backup the log regularly
so the space can be re-used, instead of letting it grow (backup log... in
BOL).
Or
you can set the recovery mode of the database to SIMPLE RECOVERY. In this
case SQL Server truncates the log every minute, but you will only be able to
restore the database ( if a crash occurs) to the most recent whole(or
differential) database backup...
If I might, take a look at the book Admin 911 by Brian Knight... It will go
a long way to get you in good shape.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>

Compress Db

Hi ,
My database in SQL server keep on increasing daily
after trasactional data keeps on updates to the database
and the database size now already more than 10 gigabytes.
1) How can I compress this database to make the size
smaller ?
2) What is the advisible way to maintain such huge
database ?
3) Beside indexing the tables , any other way to make the
database perform well ?
Please guide me.
TQHi
Have you try DBCC SHRINKFILE or DBCC SHRINKDATABASE?
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>|||Hi,
Can you identify the usage of Data and Log first using the command
sp_helpdb <dbname>
see the value of the column size, if the trasnaction log size is big then
use the below command to identify the exact usage.
dbcc sqlperf(logspace)
If the size of the log is huge and if the utilization percentage is very
less then you can shrink the transaction based on the URI's post.
steps:-
1. Perform a transaction log backup (backup log command)
2. DBCC SHRINKFILE('logical_ldf_name','truncateo
nly')
Now the LDF file will be shrinked. verify using dbcc sqlperf(logspace)
Note:
You can also check the data and index utlization using:- If you have a small
value do not shrink the MDF file.
use <dbname>
go
sp_spaceused
If the unused value is very high , then you can shrink the mdf file as well.
Thanks
Hari
MCDBA
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>|||If the log is the file which is huge, then either backup the log regularly
so the space can be re-used, instead of letting it grow (backup log... in
BOL).
Or
you can set the recovery mode of the database to SIMPLE RECOVERY. In this
case SQL Server truncates the log every minute, but you will only be able to
restore the database ( if a crash occurs) to the most recent whole(or
differential) database backup...
If I might, take a look at the book Admin 911 by Brian Knight... It will go
a long way to get you in good shape.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>sqlsql

Compress Db

Hi ,
My database in SQL server keep on increasing daily
after trasactional data keeps on updates to the database
and the database size now already more than 10 gigabytes.
1) How can I compress this database to make the size
smaller ?
2) What is the advisible way to maintain such huge
database ?
3) Beside indexing the tables , any other way to make the
database perform well ?
Please guide me.
TQHi
Have you try DBCC SHRINKFILE or DBCC SHRINKDATABASE?
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>|||Hi,
Can you identify the usage of Data and Log first using the command
sp_helpdb <dbname>
see the value of the column size, if the trasnaction log size is big then
use the below command to identify the exact usage.
dbcc sqlperf(logspace)
If the size of the log is huge and if the utilization percentage is very
less then you can shrink the transaction based on the URI's post.
steps:-
1. Perform a transaction log backup (backup log command)
2. DBCC SHRINKFILE('logical_ldf_name','truncateonly')
Now the LDF file will be shrinked. verify using dbcc sqlperf(logspace)
Note:
You can also check the data and index utlization using:- If you have a small
value do not shrink the MDF file.
use <dbname>
go
sp_spaceused
If the unused value is very high , then you can shrink the mdf file as well.
Thanks
Hari
MCDBA
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>|||If the log is the file which is huge, then either backup the log regularly
so the space can be re-used, instead of letting it grow (backup log... in
BOL).
Or
you can set the recovery mode of the database to SIMPLE RECOVERY. In this
case SQL Server truncates the log every minute, but you will only be able to
restore the database ( if a crash occurs) to the most recent whole(or
differential) database backup...
If I might, take a look at the book Admin 911 by Brian Knight... It will go
a long way to get you in good shape.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>

compress database....

I work with MSDE, now the dimensions are of 2 GB...I have deleted many pages, is possible to compress the database?

Thanks in advanced

What is likely taking up so much space is the transaction log. Each time you perform data updates MSDE (SQL Server, actually) keeps a record of this in a transaction log. Performing a BACKUP operation will automatically clear out the transaction log (because for MSDE the "truncate log on checkpoint" opton is automatically set to ON).

Have you performed a BACKUP of your MSDE database?

Alternately, you should be able to perform aDBCC SHRINKDATABASE.

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
Claudia
Hello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
>> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
>> backing up databases onto compressed volumes is not recommended and not
>> supported. My question is: Is it ok/supported if we backup the databases
>> regularly and then zip the backup files?
>> Thanks in advance.
>> Claudia
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
>> Sure, but have you restored from the compressed volume? ;-)
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>> Seriously, I think there's a few reasons MS recommends against this, but
>> I haven't seen problems either.
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>> Also, another option is to look at some of the 3rd party backup tools out
>> there that will compress on the fly as they backup.
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsqlsql