Char and Varchar: space

Showing posts with label space. Show all posts

Tuesday, March 20, 2012

Check free space on database

You can use xp_fixeddrives to check the available space on disk drives, but
is there a stored procedure to check and see how much space is available in a
database?
What I would like to do before running our jobs is first check and see if
there is space in the database. If not, then check the disk drives to see if
there is room for the database to grow.
Thanks,sp_spaceused
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" wrote:
> You can use xp_fixeddrives to check the available space on disk drives, but
> is there a stored procedure to check and see how much space is available in a
> database?
> What I would like to do before running our jobs is first check and see if
> there is space in the database. If not, then check the disk drives to see if
> there is room for the database to grow.
> Thanks,|||Is it possible to insert the results of this into a temp table and then query
it? I would like to automate the process and if the amount of free space
isn't enough, raise an error.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> sp_spaceused
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Andy" wrote:
> > You can use xp_fixeddrives to check the available space on disk drives, but
> > is there a stored procedure to check and see how much space is available in a
> > database?
> >
> > What I would like to do before running our jobs is first check and see if
> > there is space in the database. If not, then check the disk drives to see if
> > there is room for the database to grow.
> >
> > Thanks,|||Hi,
See the below script:-
create table #space(spaceused varchar(1000))
execute master.dbo.xp_cmdshell 'osql -SSQLServername -E -Q"exec
sp_spaceused" -o"c:\\sp_space.txt" -s"" '
insert into #spaceexecute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt"
'
select spaceused from #space
Thanks
Hari
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:7B459A15-783C-45FC-BFD4-E6678015B44A@.microsoft.com...
> Is it possible to insert the results of this into a temp table and then
> query
> it? I would like to automate the process and if the amount of free space
> isn't enough, raise an error.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
>> sp_spaceused
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Andy" wrote:
>> > You can use xp_fixeddrives to check the available space on disk drives,
>> > but
>> > is there a stored procedure to check and see how much space is
>> > available in a
>> > database?
>> >
>> > What I would like to do before running our jobs is first check and see
>> > if
>> > there is space in the database. If not, then check the disk drives to
>> > see if
>> > there is room for the database to grow.
>> >
>> > Thanks,

Check free space on database

Thursday, March 8, 2012

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select * from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select * from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192 from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.

I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?

Anyone?

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select*from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select*from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Anyone?

Thursday, February 16, 2012

CHARINDEX and STUFF/REPLACE

Hello,

I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space characters after each entry, so a simple replace function
will replace all the spaces after it with what I want!

I have thought of RTRIM to get rid of the spaces after and then replace, I
have also thought of CHARINDEX to find the first occurance of a space and
STUFF to replace it.
I have done my homework on these functions!
But I am having trouble writing such a statement,
I've never written a query which would use more then one function on one
column you see and I am getting confused!

I'll tell you what I want to do in simple steps

Replace only the first found space in a name column, but then if a name has
a middle initial that will be a problem,
Replace that with a dot.
then concatanate '@.emailaddress;@.emailaddress2' after it
so when SQLServer does the select it will bring back something like

joe.bloggs@.emailaddress;emailaddress
But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress

The data in the column looks like this at the moment

joe bloggs
But I guess there may come a time when we have
joe n bloggs, just to complicate things!

What is your advice, and how do I write a query like this
I have been playing around with it in Query Analyser but as I said I am
getting confused and need some help if you don't mind

Thanks a lot to all who reply :-)

Regards

JayneHi

Try:

SELECT CHARINDEX(SPACE(1),[String] ) AS [Position],[String],
STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1, SPACE(0) ) AS
[NewString]
FROM ( SELECT 'ABC DE FG' AS [String]
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT 'ABCDEFG'
) A

As an update statement

UPDATE MyTable
SET [String] = STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1,
SPACE(0) )
WHERE CHARINDEX(CHAR(32),[String] ) > 0
John

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:v7ibg2-thj.ln1@.tiger.sphynx...
> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have
> umpteen space characters after each entry, so a simple replace function
> will replace all the spaces after it with what I want!
> I have thought of RTRIM to get rid of the spaces after and then replace, I
> have also thought of CHARINDEX to find the first occurance of a space and
> STUFF to replace it.
> I have done my homework on these functions!
> But I am having trouble writing such a statement,
> I've never written a query which would use more then one function on one
> column you see and I am getting confused!
> I'll tell you what I want to do in simple steps
> Replace only the first found space in a name column, but then if a name
> has
> a middle initial that will be a problem,
> Replace that with a dot.
> then concatanate '@.emailaddress;@.emailaddress2' after it
> so when SQLServer does the select it will bring back something like
> joe.bloggs@.emailaddress;emailaddress
> But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress
> The data in the column looks like this at the moment
> joe bloggs
> But I guess there may come a time when we have
> joe n bloggs, just to complicate things!
> What is your advice, and how do I write a query like this
> I have been playing around with it in Query Analyser but as I said I am
> getting confused and need some help if you don't mind
> Thanks a lot to all who reply :-)
> Regards
> Jayne|||Little PussyCat wrote:

> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have umpteen space characters after each entry, so a simple replace
> function will replace all the spaces after it with what I want!

Its ok, I've found a way to do it.
firstly I don't want extra spaces after each name anyway so I do

Update [tablename]
SET [columnname] = RTRIM(Columnname]

Then I just do SELECT REPLACE(ColumnName,' ','.') +
'@.emailaddress;@.emailaddress'
which replaces all of the spaces in the name column and puts a dot in and
also takes into account people's names who have a middle initial! then
appends the rest of the email address in.

Thanks anyway,

Jayne

Sunday, February 12, 2012

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

create table table2

(

col2 char(2)

)

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

create table table2

(

col2 char(2)

)

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan Matic
Hi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>
|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using
:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

Char Trim

Hi,

I imported records from text file using BCP, in one column I am seeing blank space in starting as well in end. I printed the ASCII code it turn out as 32. But when I tried to trim with LTRIM and RTRIM it didn't work. I tried to replace the character using REPLACE witn no avail.

Please help.

Thanks.

You could try the following:

RIGHT(LEFT(ColumnName, LEN(ColumnName) - 1), LEN(ColumnName) - 2)

This will trim the leading and trailing characters.

Is there a chance you could you check the results of the following just to confirm that the ASCII value of the character is 32 ? :

SELECT TOP 1 ASCII(LEFT(ColumnName, 1))
FROM MyTable

Thanks

Chris

|||

I confirmed that the char is 32. I am still seeing the blanks before and after the values.

|||Is the column CHAR or VARCHAR or NVARCHAR?|||

As Dan states, the datatype is interesting here, but if you are using rtrim and ltrim, it shouldn't matter... Please post some sample code like this so we can get an idea of what you are really wanting...

drop table test
go
create table test
(
charValue char(10),
varcharValue varchar(10)
)
go
insert into test
select 'Test' + char(32) ,
'Test' + char(32)
go
select '*' + charValue + '*' as char1,
'*' + ltrim(rtrim(charValue)) + '*' as char2,
'*' + varcharValue + '*' as varchar1,
'*' + ltrim(rtrim(varcharValue)) + '*' as varchar2
from test
go
char1 char2 varchar1 varchar2
*Test * *Test* *Test * *Test*

|||

Louis,

I was surprised to see that your varcharValue included the trailing space. I thought VARCHAR columns removed trailing spaces. So then I went to study the references and found that it depends on ANSI_PADDING in the database.

http://msdn2.microsoft.com/en-us/library/ms175055.aspx

The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.

Thanks for the education! ;-)

|||Thanks for the added info...|||

Hi,

Thaks for your help.

Column is Varchar(50) also I am seeing following property of column (please note I am using SQLServer 2005 SP1).

TrimTrailingBlank =0

FixedLenNullinSource=0

This column is contains database sizes like 2000 MB

I need to remove the MB thing which I am doing

Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize)))

then I have to convert to Numeric type but when I am trying

Cast(ltrim(rtrim(Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize))))) as Numeric(20,2))

getting error

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric.

Thanks

|||

I think the computation for the third parameter of SUBSTRING is not correct. I think you just want "(CharIndex('M',DatabaseSize)-1)".

declare @.DatabaseSize varchar(50)
set @.DatabaseSize = ' 2000 MB '

print ' ' + cast(LEN(@.DatabaseSize) as varchar)
print ' ' + cast(CharIndex('M',@.DatabaseSize) as varchar)

print ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as Numeric(20,2))
print cast(len(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))) as varchar)
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as binary)

Dan

|||

Thanks a lot,

Reason I put Len() function cause we do have some rows where MB was missing so what I did I concanated 'MB' and use ChartIdex -1 and it is working now !!!! All along I was looking at wrong place to resolve the problem.

Cheers.

Tuesday, March 20, 2012

Check free space on database

Check free space on database

Check free space on database

Thursday, March 8, 2012

Check all databases for file space used

Check all databases for file space used

Thursday, February 16, 2012

CHARINDEX and STUFF/REPLACE

Sunday, February 12, 2012

char(1) and char(2) take same space?

char(1) and char(2) take same space?

char vs varchar - reclaiming free space

char vs varchar - reclaiming free space

char vs varchar - reclaiming free space

Char Trim

Char and Varchar

Blog Archive

About Me