LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1881
Members Online:
1
Guests Online:
107

Total Post History
Posts:
81455
Topics:
18718

7-Day Post History
New Posts:
1
New Topics:
1
Active Topics:
1

Our newest member
jusnailtop

Other

FAQ
All times are UTC [ DST ]

Why I love data compression

Please wait...

Why I love data compression

Postby SQLDenis on Mon Mar 26, 2012 6:45 pm

Here is why I love data compression. I compressed some older archive tables over the weekend. Gained almost 200GB of additional space in the DB :-)

  1. select
  2.     CONVERT(DATE,GETDATE()) AS [Date],
  3.     a.FILEID,
  4.     [FILE_SIZE_MB] =
  5.         convert(decimal(12,2),round(a.size/128.000,2)),
  6.     [SPACE_USED_MB] =
  7.         convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
  8.     [FREE_SPACE_MB] =
  9.         convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
  10.     NAME = left(a.NAME,35),
  11.     FILENAME = left(a.FILENAME,60)
  12. from
  13.     dbo.sysfiles a



Before

FILEID	FILE_SIZE_MB	SPACE_USED_MB	FREE_SPACE_MB
1 179353.81 162922.13 16431.69
2 64.01 14.33 49.68
3 297089.13 265538.44 31550.69
4 344555.69 298126.69 46429.00
5 165258.50 123946.63 41311.88



After

FILEID	FILE_SIZE_MB	SPACE_USED_MB	FREE_SPACE_MB
1 179353.81 110124.50 69229.31
2 2085.63 994.09 1091.54
3 297089.13 259595.44 37493.69
4 344555.69 169405.19 175150.50
5 186822.69 123962.00 62860.69
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Why I love data compression

Postby SQLDenis on Sat Mar 31, 2012 7:28 pm

Created a blog post based on this showing that you don't need to shrink the DB in order to get smaller backups after getting back some space http://blogs.lessthandot.com/index.php/ ... -t-need-to
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated