Showing posts with label space. Show all posts
Showing posts with label space. Show all posts

Friday, March 9, 2012

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.
Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv .net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>
|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv .net...
|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.c om...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
display.[vbcol=seagreen]
used/remaining[vbcol=seagreen]

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftng
p13.phx.gbl>...[vbcol=seagreen]
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display
.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.com...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
display.[vbcol=seagreen]
used/remaining[vbcol=seagreen]

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > Has anyone discovered the algorithm and the appropriate system
> > tables/columns to use to compute the actual amount of space used/remaining
> > within a database datafile?
> > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > Enterprise Manager and want to write a routine around those values.
> >
> >|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.com...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> > Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that
display.
> > You can build some really good stuff around that command.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> > news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > > Has anyone discovered the algorithm and the appropriate system
> > > tables/columns to use to compute the actual amount of space
used/remaining
> > > within a database datafile?
> > > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > > Enterprise Manager and want to write a routine around those values.
> > >
> > >

How to compress text before storing in DB?

I was planning on running a service where thousands of text messages are stored. Obviously I'd want to make the most of my DB space, and was wondering if there's some way for SQL to compress text down to the smallest space possible. If not, is there some kind of ASP component I could download to do this? Failing that, I could always write a simple one, which takes the most common letter combinations, and shortens them down to a single character.

Any advice?Space is cheap. Use it. Otherwise, your data will be dependent on whatever compression algorithm you use, and everyone will have to consult with you to read the data. Of course, if you're a consultant focused on billable hours, writing some hair-brained compression may be just what you want.

Either don't worry about space (have you done the math on what this gives you vs. buying the adequate space?), or store your text outside the db on a compressed drive.|||I don't have to consult with anyone :) It's a one-man project, just something I'm building for consumers, there's no income involved. And space is cheap, yes, if I'm hosting this solution myself. But I'm not. And SQL Server 2000 DB storage does not come cheaply at a good, reliable host.|||Anyone else? Anyone?|||Maybe this will get you to drink the water: Video on Hotmail Architecture (http://channel9.msdn.com/ShowPost.aspx?PostID=39016)

Wednesday, March 7, 2012

How to compare strings for equality in Transact SQL

I just learned that if a VARCHAR column contains 'ABC ' (three letters
and a space) then
SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
will return the record since SQL Server incorrectly pads the shorter
value with spaces to the length of the longer before comparing them. Is
there a way to compare strings for equality that works correctly?
.Bill.trim?
SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||THECOLUMN like 'ABC'
or
THECOLUMN+'$' = 'ABC'+'$'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||In SQL Server use RTRIM
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
> I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||Grant wrote:

> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
I must be missing something. Using trim gets the same incorrect result
as not using trim. The values 'ABC ' and 'ABC' are NOT equal. If I have
a table with a VARCHAR column that contains two rows with the values
'ABC ' and 'ABC', how do I write a SELECT statement that will return
the row that contains 'ABC' but not the row that contains 'ABC '?
.Bill.|||I think this is the problem he is having:
if 'ABC ' = 'ABC' print 'True' else print 'False'
Result: True
I am assuming he wants this condition to be False. In that base, he actually
doesn't want to trim the spaces.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to be
*different* (in most configurations, SQL Server considers them equal because
the trailing spaces on varchar columns are ignored).
So, using RTRIM() does not help because it yields the same result the OP is
already getting.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||T-SQL has RTRIM() and LTRIM() but no TRIM().
For other info, please see my reply to Jack.
"Grant" <email@.nowhere.com> wrote in message
news:ejQpurxMGHA.3144@.TK2MSFTNGP11.phx.gbl...
> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'|||Aaron Bertrand [SQL Server MVP] wrote:

> Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to
> be different
That is exactly what I want.
.Bill.|||> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them.
Actually, SQL Server is ignoring the trailing spaces, but the result is the
same.
There are a few ways to solve this, JT's solution is pretty simple to
implement...