Database size

Another way - Shrink database log file frequently

1. Execute this command against the database

Dump transaction YOURDBNAME with no_log

Select allOpen in new window

2. Right-Click database - Shrink Files - Select 'Reorganize pages before releasing usused space' - Enter 'Shrink File to ' - 0 - Click OK

Log file may be occupying more harddisk space. So this could save space and db size.

Raj

>>Another way - Shrink database log file frequently<<
Please do not recommend this.  This is a very bad idea.

Yes. You are right, acperkins.

>> e.g. ignore the log file, or something simular
Becuase this only I posted this comment, but not recommends this

Raj

Thanks acperkins

1.  Start backing up your Transaction Log.  This will keep it from growing.
2.  If you do not need point-in-time restores, then change the Recovery Model to Simple.

The concerning thing is it is already on simple? And i've created a few backup but the space isnt going down..

I've since found the report feature and the stat against my db at present is as follows...

Total space usage: 8,341.19Mb
Data Files:               5.532.25Mb
Transaction:            2,808.94Mb

Looking at the tranaction log space only arund 5 % is used, however regarding the data files, 95% of it appears to be data.

Any ideas what i can do to get this down?

You say there are 2M rows in the database. Dividing 5.5 GB by that gives roughly 3 kilobytes per row. Since you haven't provided any information about your database structure, it's hard to tell if this is around the right figure but it could easily be true if you have a couple of fairly wide character fields in the most commonly inserted table(s).

If you are using fixed length char columns, you might want to experiment with varchar especially if most of the time the columns are empty or very short in length.

SQL 2005 and below is based on 8K pages. I presume the 2008 is the same.

This gets into deep database design, but you can get more data into less space by doing everything by eights. So if you have a a field that could be up to 25 characters, but generally never will be that large set it as a varchar(24). Same with 50 being a varchar(48). The extra characters will force SQL to build the DB so that you have an extra page reserved for 1 or 2 characters. The rest of that 8k area will be reserved but always empty.

This also leads to efficiency for the engine. When SQL fetches (or writes) data it is still doing it in 8k blocks. So SQL is saying get these 3 8k pages with a varchar(24). When you have a varchar(25) it will fetch 4 8k pages even though the 4th page only has 1 character in it -- maybe.

With a thousand rows this doesn't show much. But when you have a million rows that adds up.

Normalizing to this level is rare, but in a large app/db it can add up.

>>Any ideas what i can do to get this down?<<
Depending on your edition you could benefit form using compression.

@Lowfatspread,

Try this: http://www.google.com/url?sa=t&source=web&cd=5&ved=0CDMQFjAE&url=http%3A%2F%2Fblogs.msdn.com%2Fcfs-filesystemfile.ashx%2F__key%2FCommunityServer-Components-PostAttachments%2F00-09-98-45-35%2FMicrosoft-SQL-Server-IO-Internals.pptx&rct=j&q=Microsoft%20SQL%20Server%20I%2FO%20Design&ei=TQ9rTYC_M8a1tgep3pDmAg&usg=AFQjCNEPQuQLjpylHPKHBqJup7qCBKqDsw&sig2=CRKGCxg2cFAzcG3jfkV5Qw&cad=rja

jimpen,
I cannot view the document from that link you posted, but I suspect that Lowfatspread is perhaps confused as I am to your reference first to the use of 8K pages and then jump to the conclusion that columns need to be in multiples of 8 bytes.

@Lowfatspread and ac,

Let me withdraw my comments. That was what I was taught in a SQL7/2K class.

Maybe it was the system at the time or just the blowhard teaching the class.

My most humblest apologies.

if object_id('dbo.RemoveNonAlphaCharacters') is not null
begin
  drop function dbo.RemoveNonAlphaCharacters
end
GO
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    While PatIndex('%[^a-z]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

    Return @TEmp
End;

GO
if object_id('dbo.RemoveNonNumericCharacters') is not null
begin
  drop function dbo.RemoveNonNumericCharacters
end
GO
Create Function [dbo].[RemoveNonNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    While PatIndex('%[^0-9]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')

    Return @TEmp
End;

GO

Declare @i as int
Declare @GUID as uniqueidentifier
Declare @Varch as varchar(36)
Declare @IntVal as bigint

if object_id('dbo.SizetestNonPaged') is not null
begin
  drop table dbo.SizetestNonPaged
end
 
Create table SizetestNonPaged
(ident	bigint identity(1,1),
 col1	varchar(25),
 col2	varchar(50),
 col3	varchar(75),
 col4   bigint);


if object_id('dbo.SizetestPaged') is not null
begin
  drop table dbo.SizetestPaged
end
 

Create table SizetestPaged
(ident	bigint identity(1,1),
 col1	varchar(24),
 col2	varchar(48),
 col3	varchar(64),
 col4	bigint);
set nocount on;
set @i = 1
while @i <= 1000000
begin
	Select	@GUID = NewId()
	Select	@Varch = DBO.RemoveNonAlphaCharacters(cast(@GUID as varchar(36)))
	Select	@IntVal = left(DBO.RemoveNonNumericCharacters(cast(@GUID as varchar(36))),10)

	insert into SizetestNonPaged 
		(col1, col2, col3, col4)
	values(left(@Varch,20), left(@Varch,30), left(@Varch,20) + '-' + left(@Varch,20), @IntVal)

	insert into SizetestPaged 
		(col1, col2, col3, col4)
	values(left(@Varch,20), left(@Varch,30), left(@Varch,20) + '-' + left(@Varch,20), @IntVal)
	if (select (@i)% (100000)) = 0 
		 print @i
	select @i = @i + 1
end
set nocount off;

--exec sp_updatestats;

exec sp_spaceused 'SizetestNonPaged';
exec sp_spaceused 'SizetestPaged';

Select allOpen in new window

Hi Everyone,
Thanks for your help, and appologies for the delay in replying, but i've decided to hire a consultant to come in and reivew this for me. I plan to highlight everything mentioned here and will get back shortly with detailed outcome / conclusion shortly.

Thanks
Webbo

Thanks everyone for your help but we never did find solution and project was cancelled for other reasons.