January 13, 2007 at 6:20 am
Heya All,
SS2K - 22gig DB, 150+ tables.
Initially this database was created using all CHAR fields so we decided to change the CHAR to VARCHAR were appropriate (we had many fields with varying lengths)
After doing this my assumption was that the database file size would reduce, instead, it has increased to 24gig.
Just wondering why that would be (hoping this isn't a really stupid question 😉 )
Steve Dingle
January 13, 2007 at 12:16 pm
you'll want to dbcc reindex the database now. basically the space that was used by the char fields is still there, and new space was added for the varchars....the system doesn't actually reuse the previous space, but gets new space and adds pointers to where the data is now.
Now that the char space is deallocated, you can reclaim it with dbcc reindex.
Lowell
January 13, 2007 at 6:27 pm
Heya Lowell,
I assume you meant DBCC DBREINDEX and I used a script to reindex all tables and now the the MDF file size is 35gig 🙁
Am I missing something, still kinda new at SQL Server
Steve Dingle
January 14, 2007 at 7:01 am
My guess is that the fields still contain the trailing spaces. Varchar doesn't trim trailing spaces, it just doesn't pad to full length.
All the reindex would have done is shift pages around, not change the records on the pages.
What you'll probably have to do (intensive and time consuming) is on each table do an update of the former char fields
Update TheTable SET TheCharColumn = rtrim(TheCharColumn)
Once've done that on all the tables, check how much space is free in the DB. If it's significant (and it should be) shrink the database to a reasonable size (not down to minimum size), allowing pages to be moved.
Once you've done that, do a full reindex of all tables as a shrink shuffles data pages randomly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2007 at 7:27 am
Heya Gail,
Ah.. makes sense about the trailing spaces. Might see about writing some code to see if I can get that done
Thanks!
Steve Dingle
January 14, 2007 at 10:29 am
Pleasure
Will you let me know if that is the case?
Easiest way to check if there are triing spaces is a query like
select '[' + TheVarcharColumn + ']' from TheTable
then you can see exactly what's been stored
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2007 at 5:56 am
Hello Gail,
Things aren't going as expected 🙁 I had created the script below to run through all the tables, get the varchar fields and trim them. After running teh sxcript I tested the data and it did infact trim the field. I then use DBCC SHRINKDATABASE to shrink it leave 10% free, and I didn't get much change at all. Just to recap, started with 22gig, went up to 24 gig after changing datatype, after trim and shrink, when to 23.9.
I then ran a script to reindex the entire database. The file size increased to 32 gig, which I then was able to shrink back down to 23.9.
Any thoughts on whats going on? As you can see i'm still relatively new to this side of SQL Server, I'm use to just workjing with it from a client.
FWIW, no need to look at the code below as I know the code below might be a bit 'verbose' but I was trying to also work with table variables 🙂 but figured I would include it.
Any thoughts would be greatly appreciated
** TRIM SCRIPT **
SET NOCOUNT ON
SET XACT_ABORT OFF
DECLARE@TableCount int, @ErrorNum int, @Message varchar(4000),
@Table_Name nvarchar(128), @TableNum int, @FieldCount int,
@FieldNum int,@Column_Name nvarchar(128),@FieldPK int,
@sqlUpdate nvarchar(4000), @Where varchar(4000)
SET @TableCount= 0
SET @FieldCount= 0
SET @ErrorNum= 0
SET @Message = ''
SET @Table_Name= ''
SET @sqlUpdate = ''
SET @Where = ''
SET @TableNum = 1
SET @FieldNum = 1
SET @FieldPK = 1
-- List of table
DECLARE @DBTables TABLE ( TABLE_NAME nvarchar(128),
pkTable int IDENTITY (1, 1) Primary key NOT NULL )
INSERT INTO @DBTables SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
-- Verify we have at least 1 table
SET @TableCount = @@ROWCOUNT
IF @TableCount > 0 BEGIN
-- Varchar Field List
DECLARE @TableFields TABLE (COLUMN_NAME nvarchar(128),
pkField int IDENTITY (1, 1) Primary key NOT NULL )
-- Move through the list of tables
WHILE @TableNum 0 BEGIN
PRINT 'Total Fields: '+CAST(@FieldCount as varchar)
SET @sqlUpdate = 'UPDATE '+@Table_Name+ ' SET '
SET @Where = ' WHERE '
SET @FieldNum = 1
WHILE @FieldNum <= @FieldCount BEGIN
SELECT @Column_Name = COLUMN_NAME
FROM @TableFields
WHERE pkField = @FieldPK
SET @sqlUpdate = @sqlUpdate + @Column_Name+ ' = LTRIM(RTRIM(' + @Column_Name + '))'
SET @Where = @Where + ' DATALENGTH( '+@Column_Name+') DATALENGTH(LTRIM(RTRIM(' + @Column_Name + ')))'
IF @FieldNum < @FieldCount BEGIN
SET @sqlUpdate = @sqlUpdate + ','
SET @Where = @Where + ' OR '
END
SET @FieldNum = @FieldNum + 1
SET @FieldPK = @FieldPK + 1
END
-- Run the Update
SET @sqlUpdate = @sqlUpdate + @Where
EXECUTE @ErrorNum = sp_executesql @sqlUpdate
IF @ErrorNum = 0 BEGIN
PRINT 'Ran: '+ @sqlUpdate
END
ELSE BEGIN
SET @Message = 'Problem updating using the following command: '+@sqlUpdate
BREAK
END
DELETE @TableFields
END
ELSE BEGIN
PRINT 'No Fields'
END
PRINT ''
SET @TableNum = @TableNum + 1
END
IF DATALENGTH( @Message ) = 0 BEGIN
SET @Message = 'Success'
END
END
ELSE BEGIN
SET @Message = 'No Tables to process'
END
PRINT @Message
Steve Dingle
January 15, 2007 at 6:40 am
Do you have a 'before' copy? Might be useful to compare sp_spaceused results table by table. I dont know if its likely or not, but you could have been off by 10% if you were almost out of free space before and you're explicitly setting 10% when you shrink.
January 15, 2007 at 10:07 am
Hello Andy,
Thanks for your reply. Unfortunately I don't. I'm actualy working on test data off-site and only have enough room for 1 set ;-). I do suppose you could be right tho I was expecting the file size to go down greatly as I had alot of empty char fields converted to varchar and then trimmed'.
What also confuses me tho is the size increase when I reindex. From 22 to 34 gig. If I then go ahead and DBCC SHRINKDATABASE i goes back down the same size. Is that normal behaviour? I'm using DBCC DBREINDEX
Steve Dingle
January 15, 2007 at 10:14 am
Rebuilding is usually going to cause the size to balloon as SQL allocates working storage for indexing/reindexing. You can reduce it some by doing indexes singly and running really frequent log backups, but its less hassle to just leave the db a little bigger and not sweat it (to a certain point!).
January 16, 2007 at 3:52 am
Don't shrink a DB after a reindex. You're just undoing all the work that reindex did.
If you absolutely have to, make sure that you use the TRUNCATEONLY clause, to just release unused extents at the end of the file, without shuffling data
No idea offhand about the size. Maybe do a spaceused on each of your tables, see which ones are hogging the space. Once you know that, you might be a bit closer to finding why.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply