CRM 3.0 DB is too large

Hi all,
We use CRM3.0 customer service for our helpdesk application with 4Gb RAM and 
Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the application 
run well but now we can't use the application because there is no space in 
server MSSQL folder. And I see that the db is too large (182 Gb for mdb file 
and 80 Gb for log file).
FYI, We've already created MSSQL Maintenance for backup the db and log. In 
the maintenance schedule, we do the full backup db every Sunday and the log 
every day. And we also do the shrink for the CRM db.
Please help me to give the solution for our problem, so our db and log is 
not large anymore and how I can get smaller db and log?

Regards,
Andry
0
Andry (1)
10/23/2007 6:34:03 AM
crm 35858 articles. 1 followers. Follow

15 Replies
605 Views

Similar Articles

[PageSpeed] 55

Your database data will increase as you use the system more and more.  You 
need to look at the physical disk space of your hard drives to ensure that 
you have enough free disk space to cater for the growth of your CRM database 
size.

You can decrease the log file size by opening the properties of the database 
and low the log file size.  

HTH's
-- 
Rgds

Michael
MCDBA 2000 | MCITP DBA 2005
http://www.mscrmschool.co.uk


"Andry" wrote:

> Hi all,
> We use CRM3.0 customer service for our helpdesk application with 4Gb RAM and 
> Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the application 
> run well but now we can't use the application because there is no space in 
> server MSSQL folder. And I see that the db is too large (182 Gb for mdb file 
> and 80 Gb for log file).
> FYI, We've already created MSSQL Maintenance for backup the db and log. In 
> the maintenance schedule, we do the full backup db every Sunday and the log 
> every day. And we also do the shrink for the CRM db.
> Please help me to give the solution for our problem, so our db and log is 
> not large anymore and how I can get smaller db and log?
> 
> Regards,
> Andry
0
Utf
10/23/2007 12:23:01 PM
Hi Andry,

cannot comment on the database size as I have no idea what the data volume 
is. The log, however, should be truncated after the successful backup so I 
suggest double-checking that log is indeed being backed up successfully.

The thing you might want to try is to manually shrink the database and log. 
From memory: in SQL Studio right-mouse click the database->Tasks->Shrink 
Database.
If files indeed shrink considerably then I suggest including shrinking as 
part of your maintenance procedure.

Hope this helps
-- 
George Doubinski
CRM Certified Professional - Developer


"Andry" <Andry@discussions.microsoft.com> wrote in message 
news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> Hi all,
> We use CRM3.0 customer service for our helpdesk application with 4Gb RAM 
> and
> Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the 
> application
> run well but now we can't use the application because there is no space in
> server MSSQL folder. And I see that the db is too large (182 Gb for mdb 
> file
> and 80 Gb for log file).
> FYI, We've already created MSSQL Maintenance for backup the db and log. In
> the maintenance schedule, we do the full backup db every Sunday and the 
> log
> every day. And we also do the shrink for the CRM db.
> Please help me to give the solution for our problem, so our db and log is
> not large anymore and how I can get smaller db and log?
>
> Regards,
> Andry 


0
George
10/23/2007 12:47:21 PM
Hi George,
I've already put shrinking db in the part of my maintenance procedure. Is 
there any idea?

Regards,
Andry

"George Doubinski" wrote:

> Hi Andry,
> 
> cannot comment on the database size as I have no idea what the data volume 
> is. The log, however, should be truncated after the successful backup so I 
> suggest double-checking that log is indeed being backed up successfully.
> 
> The thing you might want to try is to manually shrink the database and log. 
> From memory: in SQL Studio right-mouse click the database->Tasks->Shrink 
> Database.
> If files indeed shrink considerably then I suggest including shrinking as 
> part of your maintenance procedure.
> 
> Hope this helps
> -- 
> George Doubinski
> CRM Certified Professional - Developer
> 
> 
> "Andry" <Andry@discussions.microsoft.com> wrote in message 
> news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> > Hi all,
> > We use CRM3.0 customer service for our helpdesk application with 4Gb RAM 
> > and
> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the 
> > application
> > run well but now we can't use the application because there is no space in
> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb 
> > file
> > and 80 Gb for log file).
> > FYI, We've already created MSSQL Maintenance for backup the db and log. In
> > the maintenance schedule, we do the full backup db every Sunday and the 
> > log
> > every day. And we also do the shrink for the CRM db.
> > Please help me to give the solution for our problem, so our db and log is
> > not large anymore and how I can get smaller db and log?
> >
> > Regards,
> > Andry 
> 
> 
> 
0
Utf
10/24/2007 1:18:02 AM
Michael,
How can I decrease the log file?

Regards,
Andry

"Michael Langdon" wrote:

> Your database data will increase as you use the system more and more.  You 
> need to look at the physical disk space of your hard drives to ensure that 
> you have enough free disk space to cater for the growth of your CRM database 
> size.
> 
> You can decrease the log file size by opening the properties of the database 
> and low the log file size.  
> 
> HTH's
> -- 
> Rgds
> 
> Michael
> MCDBA 2000 | MCITP DBA 2005
> http://www.mscrmschool.co.uk
> 
> 
> "Andry" wrote:
> 
> > Hi all,
> > We use CRM3.0 customer service for our helpdesk application with 4Gb RAM and 
> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the application 
> > run well but now we can't use the application because there is no space in 
> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb file 
> > and 80 Gb for log file).
> > FYI, We've already created MSSQL Maintenance for backup the db and log. In 
> > the maintenance schedule, we do the full backup db every Sunday and the log 
> > every day. And we also do the shrink for the CRM db.
> > Please help me to give the solution for our problem, so our db and log is 
> > not large anymore and how I can get smaller db and log?
> > 
> > Regards,
> > Andry
0
Utf
10/24/2007 1:26:01 AM
Hi Andry,

that probably means that your log is not being backed up properly. Shrinking 
after a successful log backup should take log size to virually zero (on your 
scale of things). However, some people did report that bog-standard 
maintenance plan sometimes does not shrink logs properly.

Try this:
1. Truncate log manually using the following SQL command:

    BACKUP LOG Orgname_MSCRM WITH TRUNCATE_ONLY

2. Make a full database backup immediately after that.

3. Shrink the database and the logs

    DBCC SHRINKDATABASE(Orgname_MSCRM)

4. Repeat 3 [and rinse]. Should not be necessary in SQL 2005 but in 2000 
shrinking twice sometimes was necessary for the desired effect.

Hope this helps
-- 
George Doubinski
CRM Certified Professional - Developer


"Andry" <Andry@discussions.microsoft.com> wrote in message 
news:9651FA74-544A-470B-BD53-0BB49A172B67@microsoft.com...
> Hi George,
> I've already put shrinking db in the part of my maintenance procedure. Is
> there any idea?
>
> Regards,
> Andry
>
> "George Doubinski" wrote:
>
>> Hi Andry,
>>
>> cannot comment on the database size as I have no idea what the data 
>> volume
>> is. The log, however, should be truncated after the successful backup so 
>> I
>> suggest double-checking that log is indeed being backed up successfully.
>>
>> The thing you might want to try is to manually shrink the database and 
>> log.
>> From memory: in SQL Studio right-mouse click the database->Tasks->Shrink
>> Database.
>> If files indeed shrink considerably then I suggest including shrinking as
>> part of your maintenance procedure.
>>
>> Hope this helps
>> -- 
>> George Doubinski
>> CRM Certified Professional - Developer
>>
>>
>> "Andry" <Andry@discussions.microsoft.com> wrote in message
>> news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
>> > Hi all,
>> > We use CRM3.0 customer service for our helpdesk application with 4Gb 
>> > RAM
>> > and
>> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
>> > application
>> > run well but now we can't use the application because there is no space 
>> > in
>> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb
>> > file
>> > and 80 Gb for log file).
>> > FYI, We've already created MSSQL Maintenance for backup the db and log. 
>> > In
>> > the maintenance schedule, we do the full backup db every Sunday and the
>> > log
>> > every day. And we also do the shrink for the CRM db.
>> > Please help me to give the solution for our problem, so our db and log 
>> > is
>> > not large anymore and how I can get smaller db and log?
>> >
>> > Regards,
>> > Andry
>>
>>
>> 


0
George
10/24/2007 3:51:35 PM
Something also seems kinda odd with these sizes.  182 GB for the DB is 
pretty large for a CRM database.  When you consider the amount of data 
required to get to that size, that's a LOT of records in the system.  I have 
seen systems with large numbers of users that aren't at even 50GB.  It might 
help to get an idea which tables are taking up the space.  Beside backups, 
has any maintenance (like reorgs) been done on the system?  Was the DB 
growth parms altered from the default?  The default growth aprms are 
terrible and if they were left and the DB has grown to 180GB, then there is 
going to be a huge number of small extents that have been allocated over 
time and a LOT of fragmentation.

Also, you say you are doing backups, but are you purging old backups?  What 
size are the Full backups?  That should give you an idea of how much data is 
really in the DB as well.

-- 

Matt Parks
MVP - Dynamics CRM


"George Doubinski" <georged.delete@alexanders.remove.net.au> wrote in 
message news:%23mdUBXlFIHA.284@TK2MSFTNGP02.phx.gbl...
Hi Andry,

that probably means that your log is not being backed up properly. Shrinking
after a successful log backup should take log size to virually zero (on your
scale of things). However, some people did report that bog-standard
maintenance plan sometimes does not shrink logs properly.

Try this:
1. Truncate log manually using the following SQL command:

    BACKUP LOG Orgname_MSCRM WITH TRUNCATE_ONLY

2. Make a full database backup immediately after that.

3. Shrink the database and the logs

    DBCC SHRINKDATABASE(Orgname_MSCRM)

4. Repeat 3 [and rinse]. Should not be necessary in SQL 2005 but in 2000
shrinking twice sometimes was necessary for the desired effect.

Hope this helps
-- 
George Doubinski
CRM Certified Professional - Developer


"Andry" <Andry@discussions.microsoft.com> wrote in message
news:9651FA74-544A-470B-BD53-0BB49A172B67@microsoft.com...
> Hi George,
> I've already put shrinking db in the part of my maintenance procedure. Is
> there any idea?
>
> Regards,
> Andry
>
> "George Doubinski" wrote:
>
>> Hi Andry,
>>
>> cannot comment on the database size as I have no idea what the data
>> volume
>> is. The log, however, should be truncated after the successful backup so
>> I
>> suggest double-checking that log is indeed being backed up successfully.
>>
>> The thing you might want to try is to manually shrink the database and
>> log.
>> From memory: in SQL Studio right-mouse click the database->Tasks->Shrink
>> Database.
>> If files indeed shrink considerably then I suggest including shrinking as
>> part of your maintenance procedure.
>>
>> Hope this helps
>> -- 
>> George Doubinski
>> CRM Certified Professional - Developer
>>
>>
>> "Andry" <Andry@discussions.microsoft.com> wrote in message
>> news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
>> > Hi all,
>> > We use CRM3.0 customer service for our helpdesk application with 4Gb
>> > RAM
>> > and
>> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
>> > application
>> > run well but now we can't use the application because there is no space
>> > in
>> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb
>> > file
>> > and 80 Gb for log file).
>> > FYI, We've already created MSSQL Maintenance for backup the db and log.
>> > In
>> > the maintenance schedule, we do the full backup db every Sunday and the
>> > log
>> > every day. And we also do the shrink for the CRM db.
>> > Please help me to give the solution for our problem, so our db and log
>> > is
>> > not large anymore and how I can get smaller db and log?
>> >
>> > Regards,
>> > Andry
>>
>>
>>



0
Matt
10/24/2007 9:33:52 PM
if you are using SQL 2005, there is a disk usage report (Management Studio, 
click on the database, then the report icon on the summary page)
one of the options is Disk usage by Table.
If you could tell me which table is taking up alot of space? it will also 
give you some idea on how much index overhead you have.

first I would check is dbo.annotationbase then the Sales Literature tables, 
then I would take the next step which is obivous.

-- 
Warren Shrimpton
Leading Solutions


"Matt Parks" wrote:

> Something also seems kinda odd with these sizes.  182 GB for the DB is 
> pretty large for a CRM database.  When you consider the amount of data 
> required to get to that size, that's a LOT of records in the system.  I have 
> seen systems with large numbers of users that aren't at even 50GB.  It might 
> help to get an idea which tables are taking up the space.  Beside backups, 
> has any maintenance (like reorgs) been done on the system?  Was the DB 
> growth parms altered from the default?  The default growth aprms are 
> terrible and if they were left and the DB has grown to 180GB, then there is 
> going to be a huge number of small extents that have been allocated over 
> time and a LOT of fragmentation.
> 
> Also, you say you are doing backups, but are you purging old backups?  What 
> size are the Full backups?  That should give you an idea of how much data is 
> really in the DB as well.
> 
> -- 
> 
> Matt Parks
> MVP - Dynamics CRM
> 
> 
> "George Doubinski" <georged.delete@alexanders.remove.net.au> wrote in 
> message news:%23mdUBXlFIHA.284@TK2MSFTNGP02.phx.gbl...
> Hi Andry,
> 
> that probably means that your log is not being backed up properly. Shrinking
> after a successful log backup should take log size to virually zero (on your
> scale of things). However, some people did report that bog-standard
> maintenance plan sometimes does not shrink logs properly.
> 
> Try this:
> 1. Truncate log manually using the following SQL command:
> 
>     BACKUP LOG Orgname_MSCRM WITH TRUNCATE_ONLY
> 
> 2. Make a full database backup immediately after that.
> 
> 3. Shrink the database and the logs
> 
>     DBCC SHRINKDATABASE(Orgname_MSCRM)
> 
> 4. Repeat 3 [and rinse]. Should not be necessary in SQL 2005 but in 2000
> shrinking twice sometimes was necessary for the desired effect.
> 
> Hope this helps
> -- 
> George Doubinski
> CRM Certified Professional - Developer
> 
> 
> "Andry" <Andry@discussions.microsoft.com> wrote in message
> news:9651FA74-544A-470B-BD53-0BB49A172B67@microsoft.com...
> > Hi George,
> > I've already put shrinking db in the part of my maintenance procedure. Is
> > there any idea?
> >
> > Regards,
> > Andry
> >
> > "George Doubinski" wrote:
> >
> >> Hi Andry,
> >>
> >> cannot comment on the database size as I have no idea what the data
> >> volume
> >> is. The log, however, should be truncated after the successful backup so
> >> I
> >> suggest double-checking that log is indeed being backed up successfully.
> >>
> >> The thing you might want to try is to manually shrink the database and
> >> log.
> >> From memory: in SQL Studio right-mouse click the database->Tasks->Shrink
> >> Database.
> >> If files indeed shrink considerably then I suggest including shrinking as
> >> part of your maintenance procedure.
> >>
> >> Hope this helps
> >> -- 
> >> George Doubinski
> >> CRM Certified Professional - Developer
> >>
> >>
> >> "Andry" <Andry@discussions.microsoft.com> wrote in message
> >> news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> >> > Hi all,
> >> > We use CRM3.0 customer service for our helpdesk application with 4Gb
> >> > RAM
> >> > and
> >> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
> >> > application
> >> > run well but now we can't use the application because there is no space
> >> > in
> >> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb
> >> > file
> >> > and 80 Gb for log file).
> >> > FYI, We've already created MSSQL Maintenance for backup the db and log.
> >> > In
> >> > the maintenance schedule, we do the full backup db every Sunday and the
> >> > log
> >> > every day. And we also do the shrink for the CRM db.
> >> > Please help me to give the solution for our problem, so our db and log
> >> > is
> >> > not large anymore and how I can get smaller db and log?
> >> >
> >> > Regards,
> >> > Andry
> >>
> >>
> >>
> 
> 
> 
> 
0
Utf
10/25/2007 6:05:01 AM
Hi Andry

As a very quick solution, one solution might be to set the database as 
simple (as opposed to full) and then shrink the log file (and the data file 
if you like). Once you have set the database to simple, you should be able to 
shrink the log file to 1024 Mb.

A couple of things to understand here before doing this however;
once you shrink the file, as simple, you will loose all your transaction 
information, so take a full backup (if possible) before you do this shrink. 
That is if you do care about all those transactions.
And dont forget to set the database back to full mode, once you have shrunk 
the file.

This is a quick and simple way to gain some space back urgently, but wont 
solve the root of the problem. 

I agree with Matt Parks - thats some pretty big databases youve got there - 
perhaps you just need to check the amount of reserved space in the mdf and 
ldf files, and compare that with the amount of space that is actually being 
used for data, within those files. 

For instance, an mdf file that in windows explorer appears to be say 5o GB, 
may only ahve 35 GB of actual data being used - the other 15 GB is 'reserved 
space' in the mdf file. If the database needs to grow, then this reserved 
space is already sittign there, as the server takes a bit of a hit when SQL 
goes to acquire some more "growth" room for the database. Perhaps for some 
reason your SQL Server has got eyes that are far far bigger than its stomach?

Ashley
 

"George Doubinski" wrote:

> Hi Andry,
> 
> cannot comment on the database size as I have no idea what the data volume 
> is. The log, however, should be truncated after the successful backup so I 
> suggest double-checking that log is indeed being backed up successfully.
> 
> The thing you might want to try is to manually shrink the database and log. 
> From memory: in SQL Studio right-mouse click the database->Tasks->Shrink 
> Database.
> If files indeed shrink considerably then I suggest including shrinking as 
> part of your maintenance procedure.
> 
> Hope this helps
> -- 
> George Doubinski
> CRM Certified Professional - Developer
> 
> 
> "Andry" <Andry@discussions.microsoft.com> wrote in message 
> news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> > Hi all,
> > We use CRM3.0 customer service for our helpdesk application with 4Gb RAM 
> > and
> > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the 
> > application
> > run well but now we can't use the application because there is no space in
> > server MSSQL folder. And I see that the db is too large (182 Gb for mdb 
> > file
> > and 80 Gb for log file).
> > FYI, We've already created MSSQL Maintenance for backup the db and log. In
> > the maintenance schedule, we do the full backup db every Sunday and the 
> > log
> > every day. And we also do the shrink for the CRM db.
> > Please help me to give the solution for our problem, so our db and log is
> > not large anymore and how I can get smaller db and log?
> >
> > Regards,
> > Andry 
> 
> 
> 
0
Utf
10/29/2007 5:02:00 AM
Hi Andry. 

Seems I have to do this for several clients a week.

If your using 2005 it's a bit easier.

Start a query.
use bigfirm_MSCRM
select * from sysfiles
(Note the file ID for the Log is 2.)
Backup log bigfirm_MSCRM to disk = '\\offserverfilestore\bigfirm_MSCRM.TRN, 
Truncateonly'
Backup log bigfirm_MSCRM to disk = '\\offserverfilestore\bigfirm_MSCRM.TRN'
DBCC Shrinkfile (2,TRUNCATEONLY)
DBCC Shrinkfile (2,100)
Backup log bigfirm_MSCRM to disk = '\\offserverfilestore\bigfirm_MSCRM.TRN'
Select from *.sysfiles

Now tell us what the size of the log is.


Make sure your in a full model.

Run these one line at a time so you can watch the progress and then create a 
SQL job.

Hope this helps.
/:>




"Ashley" wrote:

> Hi Andry
> 
> As a very quick solution, one solution might be to set the database as 
> simple (as opposed to full) and then shrink the log file (and the data file 
> if you like). Once you have set the database to simple, you should be able to 
> shrink the log file to 1024 Mb.
> 
> A couple of things to understand here before doing this however;
> once you shrink the file, as simple, you will loose all your transaction 
> information, so take a full backup (if possible) before you do this shrink. 
> That is if you do care about all those transactions.
> And dont forget to set the database back to full mode, once you have shrunk 
> the file.
> 
> This is a quick and simple way to gain some space back urgently, but wont 
> solve the root of the problem. 
> 
> I agree with Matt Parks - thats some pretty big databases youve got there - 
> perhaps you just need to check the amount of reserved space in the mdf and 
> ldf files, and compare that with the amount of space that is actually being 
> used for data, within those files. 
> 
> For instance, an mdf file that in windows explorer appears to be say 5o GB, 
> may only ahve 35 GB of actual data being used - the other 15 GB is 'reserved 
> space' in the mdf file. If the database needs to grow, then this reserved 
> space is already sittign there, as the server takes a bit of a hit when SQL 
> goes to acquire some more "growth" room for the database. Perhaps for some 
> reason your SQL Server has got eyes that are far far bigger than its stomach?
> 
> Ashley
>  
> 
> "George Doubinski" wrote:
> 
> > Hi Andry,
> > 
> > cannot comment on the database size as I have no idea what the data volume 
> > is. The log, however, should be truncated after the successful backup so I 
> > suggest double-checking that log is indeed being backed up successfully.
> > 
> > The thing you might want to try is to manually shrink the database and log. 
> > From memory: in SQL Studio right-mouse click the database->Tasks->Shrink 
> > Database.
> > If files indeed shrink considerably then I suggest including shrinking as 
> > part of your maintenance procedure.
> > 
> > Hope this helps
> > -- 
> > George Doubinski
> > CRM Certified Professional - Developer
> > 
> > 
> > "Andry" <Andry@discussions.microsoft.com> wrote in message 
> > news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> > > Hi all,
> > > We use CRM3.0 customer service for our helpdesk application with 4Gb RAM 
> > > and
> > > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the 
> > > application
> > > run well but now we can't use the application because there is no space in
> > > server MSSQL folder. And I see that the db is too large (182 Gb for mdb 
> > > file
> > > and 80 Gb for log file).
> > > FYI, We've already created MSSQL Maintenance for backup the db and log. In
> > > the maintenance schedule, we do the full backup db every Sunday and the 
> > > log
> > > every day. And we also do the shrink for the CRM db.
> > > Please help me to give the solution for our problem, so our db and log is
> > > not large anymore and how I can get smaller db and log?
> > >
> > > Regards,
> > > Andry 
> > 
> > 
> > 
0
Utf
11/1/2007 1:12:00 AM
Hi Curt,

just curious, what's the intent of backing up log to disk immediately after 
it's been truncated? AFAIK, the log is useless at this point in time and the 
best thing to do at this point in time is a full database backup.

Also, what does DBCC Shrinkfile (2,100) do?

Cheers
-- 
George Doubinski
CRM Certified Professional - Developer


"Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in message 
news:1407D9C5-1CE4-4FCC-A1D9-C23EE5A77ED5@microsoft.com...
> Hi Andry.
>
> Seems I have to do this for several clients a week.
>
> If your using 2005 it's a bit easier.
>
> Start a query.
> use bigfirm_MSCRM
> select * from sysfiles
> (Note the file ID for the Log is 2.)
> Backup log bigfirm_MSCRM to disk = 
> '\\offserverfilestore\bigfirm_MSCRM.TRN,
> Truncateonly'
> Backup log bigfirm_MSCRM to disk = 
> '\\offserverfilestore\bigfirm_MSCRM.TRN'
> DBCC Shrinkfile (2,TRUNCATEONLY)
> DBCC Shrinkfile (2,100)
> Backup log bigfirm_MSCRM to disk = 
> '\\offserverfilestore\bigfirm_MSCRM.TRN'
> Select from *.sysfiles
>
> Now tell us what the size of the log is.
>
>
> Make sure your in a full model.
>
> Run these one line at a time so you can watch the progress and then create 
> a
> SQL job.
>
> Hope this helps.
> /:>
>
>
>
>
> "Ashley" wrote:
>
>> Hi Andry
>>
>> As a very quick solution, one solution might be to set the database as
>> simple (as opposed to full) and then shrink the log file (and the data 
>> file
>> if you like). Once you have set the database to simple, you should be 
>> able to
>> shrink the log file to 1024 Mb.
>>
>> A couple of things to understand here before doing this however;
>> once you shrink the file, as simple, you will loose all your transaction
>> information, so take a full backup (if possible) before you do this 
>> shrink.
>> That is if you do care about all those transactions.
>> And dont forget to set the database back to full mode, once you have 
>> shrunk
>> the file.
>>
>> This is a quick and simple way to gain some space back urgently, but wont
>> solve the root of the problem.
>>
>> I agree with Matt Parks - thats some pretty big databases youve got 
>> there -
>> perhaps you just need to check the amount of reserved space in the mdf 
>> and
>> ldf files, and compare that with the amount of space that is actually 
>> being
>> used for data, within those files.
>>
>> For instance, an mdf file that in windows explorer appears to be say 5o 
>> GB,
>> may only ahve 35 GB of actual data being used - the other 15 GB is 
>> 'reserved
>> space' in the mdf file. If the database needs to grow, then this reserved
>> space is already sittign there, as the server takes a bit of a hit when 
>> SQL
>> goes to acquire some more "growth" room for the database. Perhaps for 
>> some
>> reason your SQL Server has got eyes that are far far bigger than its 
>> stomach?
>>
>> Ashley
>>
>>
>> "George Doubinski" wrote:
>>
>> > Hi Andry,
>> >
>> > cannot comment on the database size as I have no idea what the data 
>> > volume
>> > is. The log, however, should be truncated after the successful backup 
>> > so I
>> > suggest double-checking that log is indeed being backed up 
>> > successfully.
>> >
>> > The thing you might want to try is to manually shrink the database and 
>> > log.
>> > From memory: in SQL Studio right-mouse click the 
>> > database->Tasks->Shrink
>> > Database.
>> > If files indeed shrink considerably then I suggest including shrinking 
>> > as
>> > part of your maintenance procedure.
>> >
>> > Hope this helps
>> > -- 
>> > George Doubinski
>> > CRM Certified Professional - Developer
>> >
>> >
>> > "Andry" <Andry@discussions.microsoft.com> wrote in message
>> > news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
>> > > Hi all,
>> > > We use CRM3.0 customer service for our helpdesk application with 4Gb 
>> > > RAM
>> > > and
>> > > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
>> > > application
>> > > run well but now we can't use the application because there is no 
>> > > space in
>> > > server MSSQL folder. And I see that the db is too large (182 Gb for 
>> > > mdb
>> > > file
>> > > and 80 Gb for log file).
>> > > FYI, We've already created MSSQL Maintenance for backup the db and 
>> > > log. In
>> > > the maintenance schedule, we do the full backup db every Sunday and 
>> > > the
>> > > log
>> > > every day. And we also do the shrink for the CRM db.
>> > > Please help me to give the solution for our problem, so our db and 
>> > > log is
>> > > not large anymore and how I can get smaller db and log?
>> > >
>> > > Regards,
>> > > Andry
>> >
>> >
>> > 


0
George
11/2/2007 4:00:54 AM
Not a problem George.

As you can imagine, I have several clients a month who do things like turn 
off the SQL agent or change passwords on accounts that run services.

Or they did things themselves.

So I have seen some really stubborn LDFs that would not "Give up the Ghost".
All Transaction should be committed but just in case I run:
DBCC Opentran 
Against the LDF 

DBCC Shrinkfile (2,100) shrinks the file to 100 mg.  If you can go lower , 
then great.

I backup them up afterward to see the backup size. Often I see a very small 
TRN file and a huge LDF.
So it's verification.  I delete them later.
/:>
 Yet I also see great maintenance plan backups and still the LDF is huge.

More to say on that another time.
/:>


"George Doubinski" wrote:

> Hi Curt,
> 
> just curious, what's the intent of backing up log to disk immediately after 
> it's been truncated? AFAIK, the log is useless at this point in time and the 
> best thing to do at this point in time is a full database backup.
> 
> Also, what does DBCC Shrinkfile (2,100) do?
> 
> Cheers
> -- 
> George Doubinski
> CRM Certified Professional - Developer
> 
> 
> "Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in message 
> news:1407D9C5-1CE4-4FCC-A1D9-C23EE5A77ED5@microsoft.com...
> > Hi Andry.
> >
> > Seems I have to do this for several clients a week.
> >
> > If your using 2005 it's a bit easier.
> >
> > Start a query.
> > use bigfirm_MSCRM
> > select * from sysfiles
> > (Note the file ID for the Log is 2.)
> > Backup log bigfirm_MSCRM to disk = 
> > '\\offserverfilestore\bigfirm_MSCRM.TRN,
> > Truncateonly'
> > Backup log bigfirm_MSCRM to disk = 
> > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > DBCC Shrinkfile (2,TRUNCATEONLY)
> > DBCC Shrinkfile (2,100)
> > Backup log bigfirm_MSCRM to disk = 
> > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > Select from *.sysfiles
> >
> > Now tell us what the size of the log is.
> >
> >
> > Make sure your in a full model.
> >
> > Run these one line at a time so you can watch the progress and then create 
> > a
> > SQL job.
> >
> > Hope this helps.
> > /:>
> >
> >
> >
> >
> > "Ashley" wrote:
> >
> >> Hi Andry
> >>
> >> As a very quick solution, one solution might be to set the database as
> >> simple (as opposed to full) and then shrink the log file (and the data 
> >> file
> >> if you like). Once you have set the database to simple, you should be 
> >> able to
> >> shrink the log file to 1024 Mb.
> >>
> >> A couple of things to understand here before doing this however;
> >> once you shrink the file, as simple, you will loose all your transaction
> >> information, so take a full backup (if possible) before you do this 
> >> shrink.
> >> That is if you do care about all those transactions.
> >> And dont forget to set the database back to full mode, once you have 
> >> shrunk
> >> the file.
> >>
> >> This is a quick and simple way to gain some space back urgently, but wont
> >> solve the root of the problem.
> >>
> >> I agree with Matt Parks - thats some pretty big databases youve got 
> >> there -
> >> perhaps you just need to check the amount of reserved space in the mdf 
> >> and
> >> ldf files, and compare that with the amount of space that is actually 
> >> being
> >> used for data, within those files.
> >>
> >> For instance, an mdf file that in windows explorer appears to be say 5o 
> >> GB,
> >> may only ahve 35 GB of actual data being used - the other 15 GB is 
> >> 'reserved
> >> space' in the mdf file. If the database needs to grow, then this reserved
> >> space is already sittign there, as the server takes a bit of a hit when 
> >> SQL
> >> goes to acquire some more "growth" room for the database. Perhaps for 
> >> some
> >> reason your SQL Server has got eyes that are far far bigger than its 
> >> stomach?
> >>
> >> Ashley
> >>
> >>
> >> "George Doubinski" wrote:
> >>
> >> > Hi Andry,
> >> >
> >> > cannot comment on the database size as I have no idea what the data 
> >> > volume
> >> > is. The log, however, should be truncated after the successful backup 
> >> > so I
> >> > suggest double-checking that log is indeed being backed up 
> >> > successfully.
> >> >
> >> > The thing you might want to try is to manually shrink the database and 
> >> > log.
> >> > From memory: in SQL Studio right-mouse click the 
> >> > database->Tasks->Shrink
> >> > Database.
> >> > If files indeed shrink considerably then I suggest including shrinking 
> >> > as
> >> > part of your maintenance procedure.
> >> >
> >> > Hope this helps
> >> > -- 
> >> > George Doubinski
> >> > CRM Certified Professional - Developer
> >> >
> >> >
> >> > "Andry" <Andry@discussions.microsoft.com> wrote in message
> >> > news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> >> > > Hi all,
> >> > > We use CRM3.0 customer service for our helpdesk application with 4Gb 
> >> > > RAM
> >> > > and
> >> > > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
> >> > > application
> >> > > run well but now we can't use the application because there is no 
> >> > > space in
> >> > > server MSSQL folder. And I see that the db is too large (182 Gb for 
> >> > > mdb
> >> > > file
> >> > > and 80 Gb for log file).
> >> > > FYI, We've already created MSSQL Maintenance for backup the db and 
> >> > > log. In
> >> > > the maintenance schedule, we do the full backup db every Sunday and 
> >> > > the
> >> > > log
> >> > > every day. And we also do the shrink for the CRM db.
> >> > > Please help me to give the solution for our problem, so our db and 
> >> > > log is
> >> > > not large anymore and how I can get smaller db and log?
> >> > >
> >> > > Regards,
> >> > > Andry
> >> >
> >> >
> >> > 
> 
> 
> 
0
Utf
11/2/2007 2:40:00 PM
The backup of the log will not shrink the LDF, that is normal behaviour and 
what you want.  under normal circumstances, this allows the log to stay at a 
stable allocation of size.  Curious is the odd behaviour you were seeing was 
in SQL 200 or 2005?  2000 had a weird thing where you needed to basically, 
truncate log, shink it, truncate log, shirink it again before it would 
release the space properly, but this was fixed in 2005.

-- 

Matt Parks
MVP - Dynamics CRM


"Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in message 
news:C22304B8-0FB0-4D64-B759-DBB23E610A9B@microsoft.com...
Not a problem George.

As you can imagine, I have several clients a month who do things like turn
off the SQL agent or change passwords on accounts that run services.

Or they did things themselves.

So I have seen some really stubborn LDFs that would not "Give up the Ghost".
All Transaction should be committed but just in case I run:
DBCC Opentran
Against the LDF

DBCC Shrinkfile (2,100) shrinks the file to 100 mg.  If you can go lower ,
then great.

I backup them up afterward to see the backup size. Often I see a very small
TRN file and a huge LDF.
So it's verification.  I delete them later.
/:>
 Yet I also see great maintenance plan backups and still the LDF is huge.

More to say on that another time.
/:>


"George Doubinski" wrote:

> Hi Curt,
>
> just curious, what's the intent of backing up log to disk immediately 
> after
> it's been truncated? AFAIK, the log is useless at this point in time and 
> the
> best thing to do at this point in time is a full database backup.
>
> Also, what does DBCC Shrinkfile (2,100) do?
>
> Cheers
> -- 
> George Doubinski
> CRM Certified Professional - Developer
>
>
> "Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in 
> message
> news:1407D9C5-1CE4-4FCC-A1D9-C23EE5A77ED5@microsoft.com...
> > Hi Andry.
> >
> > Seems I have to do this for several clients a week.
> >
> > If your using 2005 it's a bit easier.
> >
> > Start a query.
> > use bigfirm_MSCRM
> > select * from sysfiles
> > (Note the file ID for the Log is 2.)
> > Backup log bigfirm_MSCRM to disk =
> > '\\offserverfilestore\bigfirm_MSCRM.TRN,
> > Truncateonly'
> > Backup log bigfirm_MSCRM to disk =
> > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > DBCC Shrinkfile (2,TRUNCATEONLY)
> > DBCC Shrinkfile (2,100)
> > Backup log bigfirm_MSCRM to disk =
> > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > Select from *.sysfiles
> >
> > Now tell us what the size of the log is.
> >
> >
> > Make sure your in a full model.
> >
> > Run these one line at a time so you can watch the progress and then 
> > create
> > a
> > SQL job.
> >
> > Hope this helps.
> > /:>
> >
> >
> >
> >
> > "Ashley" wrote:
> >
> >> Hi Andry
> >>
> >> As a very quick solution, one solution might be to set the database as
> >> simple (as opposed to full) and then shrink the log file (and the data
> >> file
> >> if you like). Once you have set the database to simple, you should be
> >> able to
> >> shrink the log file to 1024 Mb.
> >>
> >> A couple of things to understand here before doing this however;
> >> once you shrink the file, as simple, you will loose all your 
> >> transaction
> >> information, so take a full backup (if possible) before you do this
> >> shrink.
> >> That is if you do care about all those transactions.
> >> And dont forget to set the database back to full mode, once you have
> >> shrunk
> >> the file.
> >>
> >> This is a quick and simple way to gain some space back urgently, but 
> >> wont
> >> solve the root of the problem.
> >>
> >> I agree with Matt Parks - thats some pretty big databases youve got
> >> there -
> >> perhaps you just need to check the amount of reserved space in the mdf
> >> and
> >> ldf files, and compare that with the amount of space that is actually
> >> being
> >> used for data, within those files.
> >>
> >> For instance, an mdf file that in windows explorer appears to be say 5o
> >> GB,
> >> may only ahve 35 GB of actual data being used - the other 15 GB is
> >> 'reserved
> >> space' in the mdf file. If the database needs to grow, then this 
> >> reserved
> >> space is already sittign there, as the server takes a bit of a hit when
> >> SQL
> >> goes to acquire some more "growth" room for the database. Perhaps for
> >> some
> >> reason your SQL Server has got eyes that are far far bigger than its
> >> stomach?
> >>
> >> Ashley
> >>
> >>
> >> "George Doubinski" wrote:
> >>
> >> > Hi Andry,
> >> >
> >> > cannot comment on the database size as I have no idea what the data
> >> > volume
> >> > is. The log, however, should be truncated after the successful backup
> >> > so I
> >> > suggest double-checking that log is indeed being backed up
> >> > successfully.
> >> >
> >> > The thing you might want to try is to manually shrink the database 
> >> > and
> >> > log.
> >> > From memory: in SQL Studio right-mouse click the
> >> > database->Tasks->Shrink
> >> > Database.
> >> > If files indeed shrink considerably then I suggest including 
> >> > shrinking
> >> > as
> >> > part of your maintenance procedure.
> >> >
> >> > Hope this helps
> >> > -- 
> >> > George Doubinski
> >> > CRM Certified Professional - Developer
> >> >
> >> >
> >> > "Andry" <Andry@discussions.microsoft.com> wrote in message
> >> > news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> >> > > Hi all,
> >> > > We use CRM3.0 customer service for our helpdesk application with 
> >> > > 4Gb
> >> > > RAM
> >> > > and
> >> > > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
> >> > > application
> >> > > run well but now we can't use the application because there is no
> >> > > space in
> >> > > server MSSQL folder. And I see that the db is too large (182 Gb for
> >> > > mdb
> >> > > file
> >> > > and 80 Gb for log file).
> >> > > FYI, We've already created MSSQL Maintenance for backup the db and
> >> > > log. In
> >> > > the maintenance schedule, we do the full backup db every Sunday and
> >> > > the
> >> > > log
> >> > > every day. And we also do the shrink for the CRM db.
> >> > > Please help me to give the solution for our problem, so our db and
> >> > > log is
> >> > > not large anymore and how I can get smaller db and log?
> >> > >
> >> > > Regards,
> >> > > Andry
> >> >
> >> >
> >> >
>
>
> 


0
Matt
11/2/2007 6:54:25 PM
Well, you are so right regarding SQL 2000 SP4.

But this is a problem.  For instance, I have a client where my SQL 2005 GP 
install is working great on one server.

Now there is another SQL 2005 64bit server that has the large LDF file 
problem.

Go figure.  
The service pack is on there too.  We can control it with scripts but I have 
a feeling that something else is going on.  This thread concerns CRM but it's 
a real problem in small shops with Dynamics Great Plains.   Three clients 
this week had the problem.

But there were some other problems as well, like the fact that someone moved 
the c360 database to the C drive.   Nice call, on that one.

It keeps us busy though.
Thanks for chiming in Matt.
Nice having you on the thread.

/:>


"Matt Parks" wrote:

> The backup of the log will not shrink the LDF, that is normal behaviour and 
> what you want.  under normal circumstances, this allows the log to stay at a 
> stable allocation of size.  Curious is the odd behaviour you were seeing was 
> in SQL 200 or 2005?  2000 had a weird thing where you needed to basically, 
> truncate log, shink it, truncate log, shirink it again before it would 
> release the space properly, but this was fixed in 2005.
> 
> -- 
> 
> Matt Parks
> MVP - Dynamics CRM
> 
> 
> "Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in message 
> news:C22304B8-0FB0-4D64-B759-DBB23E610A9B@microsoft.com...
> Not a problem George.
> 
> As you can imagine, I have several clients a month who do things like turn
> off the SQL agent or change passwords on accounts that run services.
> 
> Or they did things themselves.
> 
> So I have seen some really stubborn LDFs that would not "Give up the Ghost".
> All Transaction should be committed but just in case I run:
> DBCC Opentran
> Against the LDF
> 
> DBCC Shrinkfile (2,100) shrinks the file to 100 mg.  If you can go lower ,
> then great.
> 
> I backup them up afterward to see the backup size. Often I see a very small
> TRN file and a huge LDF.
> So it's verification.  I delete them later.
> /:>
>  Yet I also see great maintenance plan backups and still the LDF is huge.
> 
> More to say on that another time.
> /:>
> 
> 
> "George Doubinski" wrote:
> 
> > Hi Curt,
> >
> > just curious, what's the intent of backing up log to disk immediately 
> > after
> > it's been truncated? AFAIK, the log is useless at this point in time and 
> > the
> > best thing to do at this point in time is a full database backup.
> >
> > Also, what does DBCC Shrinkfile (2,100) do?
> >
> > Cheers
> > -- 
> > George Doubinski
> > CRM Certified Professional - Developer
> >
> >
> > "Curt Spanburgh" <CurtSpanburgh@discussions.microsoft.com> wrote in 
> > message
> > news:1407D9C5-1CE4-4FCC-A1D9-C23EE5A77ED5@microsoft.com...
> > > Hi Andry.
> > >
> > > Seems I have to do this for several clients a week.
> > >
> > > If your using 2005 it's a bit easier.
> > >
> > > Start a query.
> > > use bigfirm_MSCRM
> > > select * from sysfiles
> > > (Note the file ID for the Log is 2.)
> > > Backup log bigfirm_MSCRM to disk =
> > > '\\offserverfilestore\bigfirm_MSCRM.TRN,
> > > Truncateonly'
> > > Backup log bigfirm_MSCRM to disk =
> > > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > > DBCC Shrinkfile (2,TRUNCATEONLY)
> > > DBCC Shrinkfile (2,100)
> > > Backup log bigfirm_MSCRM to disk =
> > > '\\offserverfilestore\bigfirm_MSCRM.TRN'
> > > Select from *.sysfiles
> > >
> > > Now tell us what the size of the log is.
> > >
> > >
> > > Make sure your in a full model.
> > >
> > > Run these one line at a time so you can watch the progress and then 
> > > create
> > > a
> > > SQL job.
> > >
> > > Hope this helps.
> > > /:>
> > >
> > >
> > >
> > >
> > > "Ashley" wrote:
> > >
> > >> Hi Andry
> > >>
> > >> As a very quick solution, one solution might be to set the database as
> > >> simple (as opposed to full) and then shrink the log file (and the data
> > >> file
> > >> if you like). Once you have set the database to simple, you should be
> > >> able to
> > >> shrink the log file to 1024 Mb.
> > >>
> > >> A couple of things to understand here before doing this however;
> > >> once you shrink the file, as simple, you will loose all your 
> > >> transaction
> > >> information, so take a full backup (if possible) before you do this
> > >> shrink.
> > >> That is if you do care about all those transactions.
> > >> And dont forget to set the database back to full mode, once you have
> > >> shrunk
> > >> the file.
> > >>
> > >> This is a quick and simple way to gain some space back urgently, but 
> > >> wont
> > >> solve the root of the problem.
> > >>
> > >> I agree with Matt Parks - thats some pretty big databases youve got
> > >> there -
> > >> perhaps you just need to check the amount of reserved space in the mdf
> > >> and
> > >> ldf files, and compare that with the amount of space that is actually
> > >> being
> > >> used for data, within those files.
> > >>
> > >> For instance, an mdf file that in windows explorer appears to be say 5o
> > >> GB,
> > >> may only ahve 35 GB of actual data being used - the other 15 GB is
> > >> 'reserved
> > >> space' in the mdf file. If the database needs to grow, then this 
> > >> reserved
> > >> space is already sittign there, as the server takes a bit of a hit when
> > >> SQL
> > >> goes to acquire some more "growth" room for the database. Perhaps for
> > >> some
> > >> reason your SQL Server has got eyes that are far far bigger than its
> > >> stomach?
> > >>
> > >> Ashley
> > >>
> > >>
> > >> "George Doubinski" wrote:
> > >>
> > >> > Hi Andry,
> > >> >
> > >> > cannot comment on the database size as I have no idea what the data
> > >> > volume
> > >> > is. The log, however, should be truncated after the successful backup
> > >> > so I
> > >> > suggest double-checking that log is indeed being backed up
> > >> > successfully.
> > >> >
> > >> > The thing you might want to try is to manually shrink the database 
> > >> > and
> > >> > log.
> > >> > From memory: in SQL Studio right-mouse click the
> > >> > database->Tasks->Shrink
> > >> > Database.
> > >> > If files indeed shrink considerably then I suggest including 
> > >> > shrinking
> > >> > as
> > >> > part of your maintenance procedure.
> > >> >
> > >> > Hope this helps
> > >> > -- 
> > >> > George Doubinski
> > >> > CRM Certified Professional - Developer
> > >> >
> > >> >
> > >> > "Andry" <Andry@discussions.microsoft.com> wrote in message
> > >> > news:DCD87259-690D-48D9-B4C4-4AFA6605DEC2@microsoft.com...
> > >> > > Hi all,
> > >> > > We use CRM3.0 customer service for our helpdesk application with 
> > >> > > 4Gb
> > >> > > RAM
> > >> > > and
> > >> > > Dual Intel Xeon Processor 3.6 Ghz (DB Server). For 8 months, the
> > >> > > application
> > >> > > run well but now we can't use the application because there is no
> > >> > > space in
> > >> > > server MSSQL folder. And I see that the db is too large (182 Gb for
> > >> > > mdb
> > >> > > file
> > >> > > and 80 Gb for log file).
> > >> > > FYI, We've already created MSSQL Maintenance for backup the db and
> > >> > > log. In
> > >> > > the maintenance schedule, we do the full backup db every Sunday and
> > >> > > the
> > >> > > log
> > >> > > every day. And we also do the shrink for the CRM db.
> > >> > > Please help me to give the solution for our problem, so our db and
> > >> > > log is
> > >> > > not large anymore and how I can get smaller db and log?
> > >> > >
> > >> > > Regards,
> > >> > > Andry
> > >> >
> > >> >
> > >> >
> >
> >
> > 
> 
> 
> 
0
Utf
11/2/2007 7:06:02 PM
Andry
Run the code below in Query Analyzer then run "EXEC sp_show_huge_tables" in 
Query Analyzer on your CRM Database to give you some idea where the majority 
of your storage is. Hope this helps some.


USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND 
type = 'P')
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top			int	= NULL,
@include_system_tables 	bit 	= 0
)
AS

BEGIN
	IF @top > 0
	SET ROWCOUNT @top

	SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND 
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] 
FROM  
	(
	SELECT	QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS 
[Table Name],
		CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT 
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 
1024.)/1024.)) AS [Total space used (MB)]
	FROM	sysindexes i (NOLOCK)
			INNER JOIN 
		sysobjects o (NOLOCK) 
			ON 
		i.id = o.id AND 
		((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND 
		((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
	WHERE	indid IN (0, 1, 255)
	GROUP BY	QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
	
	) as a
ORDER BY	[Total space used (MB)] DESC


	SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public



0
Utf
11/20/2007 8:53:03 PM
Thanks Jason.

I will try this script on some Dynamics GP lab servers as well.  


"jason bell" wrote:

> Andry
> Run the code below in Query Analyzer then run "EXEC sp_show_huge_tables" in 
> Query Analyzer on your CRM Database to give you some idea where the majority 
> of your storage is. Hope this helps some.
> 
> 
> USE master
> GO
> 
> IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND 
> type = 'P')
> DROP PROC sp_show_huge_tables
> GO
> 
> CREATE PROC sp_show_huge_tables
> (
> @top			int	= NULL,
> @include_system_tables 	bit 	= 0
> )
> AS
> 
> BEGIN
> 	IF @top > 0
> 	SET ROWCOUNT @top
> 
> 	SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND 
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] 
> FROM  
> 	(
> 	SELECT	QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS 
> [Table Name],
> 		CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT 
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 
> 1024.)/1024.)) AS [Total space used (MB)]
> 	FROM	sysindexes i (NOLOCK)
> 			INNER JOIN 
> 		sysobjects o (NOLOCK) 
> 			ON 
> 		i.id = o.id AND 
> 		((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND 
> 		((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
> 	WHERE	indid IN (0, 1, 255)
> 	GROUP BY	QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> 	
> 	) as a
> ORDER BY	[Total space used (MB)] DESC
> 
> 
> 	SET ROWCOUNT 0
> END
> 
> GO
> 
> GRANT EXEC ON sp_show_huge_tables TO Public
> 
> 
> 
0
Utf
11/20/2007 9:43:01 PM
Reply:

Similar Artilces:

Upgragding to 1.2 to 1.3
Receiving the media on Monday. Any issues that I should be aware of? Pralell dongle. Best, M Manny, Why not wait til 1.3R due within the next couple of weeks? Otherwise, backup your database and any customized reports and/or receipts. If you get a license violation after the install, just unplug and re-plug in your dongle. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open wi...

Office 2004 update 11.3.9 weirdness
Installed Office 2004 update 11.3.9 when microsoft auto update pushed it down this morning. Any idea why it changed the name of my hard drive to "v bc"? Any idea what to do about that? mac book pro, OSX 10.4.10 In article <1194878374.217619.309200@19g2000hsx.googlegroups.com>, pattigibbons@nycap.rr.com wrote: > Installed Office 2004 update 11.3.9 when microsoft auto update pushed > it down this morning. Any idea why it changed the name of my hard > drive to "v bc"? Any idea what to do about that? > > mac book pro, OSX 10.4.10 The Office updat...

How do I track emails ONLY using tokens in CRM 4.0?
The pattern matching or "intelligent" matching of email content is misapplying inbound emails to non-related cases. I want to turn this feature OFF ASAP and return to ONLY tracking through tokens in the subject line. I can turn OFF tokens but not the smart matching. A configuration option would have been nice. I think you turn on the tokens, and then your users have to use the option to only track "E-mail messages in response to CRM e-mail" - that's how you prevent CRM from using 'smart' matching. Dave Ireland "Forrest_Adam" <ForrestAd...

email format #3
I have an emailing program that uses a Rich_text_box as a body. Now with rich_text_box you can have bold, multi color underlined.etc TEXT. Since using HTML body is a bad (not recommended, even thought it would keep all the formatting) thing then what else can I use to have the same type of format as an rich_text or html so when the recipient receives, it looks presentable with all it indenting, bold,.etc???? HELP!!! Only Outlook recipients can see rich-text content. HTML is a better approach if you want to reach the most people. -- Sue Mosher, Outlook MVP Author of Microsoft O...

CRM Field Names
Is there a list on field names that CRM creates in the database in order to use them for setting up workflow? ...

CRM 3.0 and CRM 4.0
Dear All, We were implementing MSCRM for a very protential CRM customer (Education). Even they haven't signed the contract but we already started developing on the CRM 3.0, 3 weeks ago. A lot of customisation + .NET developing required on the version 3 but now we knew that we're reinventing the wheels as some of them will be already built in the V4 which one will be realease very soon (Q1-2008?) If we place order to MS now, we will receive the V4 earlier than Q1-2008, but it is V4, not V3 So now we decided moving the developing on V4 directly because no guarantee what we wer...

Organizational Settings in CRM 3.0
Is it possible to hide viewing of Organizational Settings from non System Administrator users? ...

CRM and MapPoint
I have a client that has MapPoint 2004. They would like to have the ability to map all of their current clients in certain areas, also with the ability to make indication of future growth in these same areas. Currently, they can only map one facility at a time. They would like to be able to map all of their existing and future facilities for a spcific service area on one map. Does anyone know of a solution for accomplishing this? Thanks, -Rick Not a mappoint expert by any means but I think there are 2 choices: 1. Export data from CRM (can probably just use the excel export option)...

Cannot access CRM homepage on another machine
Hi, I have installed CRM 1.2 on a Windows Server 2003 SP1 machine. I can work with CRM on this machine using http://localhost. But when I try to access the homepage on another computer by typing http://CRMserver or http://IP address of CRM server I cannot access it. Can anyone please tell what do I have to configure to access the homepage on another machine? Thanks and Regards, Harshad ...

outlook express ME 5.0
Hi! My friend has a computer that we cannot delete any sent messages. Everything else seems to work. He has messages from 2007 that need to be deleted. Any help would be appreciate. Thanks, Leo ...

Reset CRM
Hi, How can I reset CRM database? I create contacts, acounts, invoices, etc., and i want delete all. Tnhank's, RS You cannot reset the database. You could restore the database if you took a backup before you started adding data. You can also go into the system and start deleting records. Can I ask why you need to do this? Have you been experimenting with CRM and now want to go to a live server? -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Ricardo" <anonymous@discussions.microsoft.com> wrote in mes...

Vista CRM 3.0 Desktop client
Can someone tell me where I can find the CRM 3.0 Desktop client which works with Vista & Office 2007? Thanks You can download it using the following link. http://www.microsoft.com/downloads/details.aspx?FamilyID=740e1b3b-11c3-4aef-b2e3-8309e0cf1bb0&DisplayLang=en Magui "circulent" <circulent@discussions.microsoft.com> wrote in message news:27D9D594-593F-4C1D-9E61-09F946FFA8BB@microsoft.com... > Can someone tell me where I can find the CRM 3.0 Desktop client which > works > with Vista & Office 2007? > > Thanks ...

From Rows to Columun #3
I have data in rows, I want to past to columns so i can import intio access... HELP Example (Current data) 3 42 42 3 38 38 4 22 I want to move to 3 42 42 3 38 38 4 22 THANKS ...

3-rd tools to Recovery edb files
Do you know any good tools to restore single mail or single mailbox from offline backup Exchange database files (edb or stm files). thanks http://www.officerecovery.com/exchangeserver/ -- My Regards, Omar El-Sherif "Luk" <only4news@gmail.com> wrote in message news:eox4HmrPGHA.2828@TK2MSFTNGP12.phx.gbl... > Do you know any good tools to restore single mail or single mailbox from > offline backup Exchange database files (edb or stm files). > > thanks try http://www.ontrack.co.uk/powercontrols/ - they've been around for a few years now and seem to have a...

How works conflictresolution with the offline outlookclient and ms crm?
Hi Does somebody know how the conflictresolution works if one ore more offlineclients synchronise to the server. How does it work if diffrent offlineclientds did some changes on the same field? Does someone know a good description about this problem? Thank's Michel Consider a situation where a field has been changed. 1. Two offline clients making a change 2. One offline and the second online client making a change when the client goes online the data present in the client is synchronised and saved irrespective of the time when the last changes were made to the field. "Hope...

CRM registration can not be done!
After CRM installation and valid internet connection via LAN, I could not succeed to register CRM 3.0. Is there anybody who had this problem before and solve it?? When you fill in the registration details, if you specify the implementation was done by a partner company, make sure you fill in data in all fields for the partner company, otherwise the registration process can fail -- David Jennaway - Microsoft Dynamics CRM MVP Web: http://www.excitation.co.uk "Pleiades" wrote: > After CRM installation and valid internet connection via LAN, I could > not succeed to registe...

Transaction Log Files #3
How do I calculate total Transaction Log file size on Exch2k3SP1 when I move mailboxes with, for example, total size of 100MB from one server to another (1 Storage Group, one Mailbox store)? -- Kind regards, Menko den Ouden NL On Wed, 5 Oct 2005 10:21:04 +0200, "Menko den Ouden" <oudenmd@exor-itNOSPAM.nl> wrote: >How do I calculate total Transaction Log file size on Exch2k3SP1 when I move >mailboxes with, for example, total size of 100MB from one server to another >(1 Storage Group, one Mailbox store)? I always assume "alot". If you are worried...

Migrating CRM to CRM
We had to delete the OU from the Active Directy, and now cannot get to our data. Reading through the posts it looks as though we have to use the DMF to convert our data because of the GUIDs that are hard-coded in the files. Unless someone knows of a utility that I can use to change the GUIDs in the Active Directory - but we tried a number of different things. So, is there an easy method to import CRM data into CRM? I tried using SQL DTS to the cdf_entities, but this is like pulling teeth, and don't know if I am going to be losing any data. Anyone have any insight into getti...

PDF's not traking in crm
when tracking a pdf file in CRM we get an error saying that we can not track it. the files are small so it is not an issue with them being to big. we can track other types of attachments with no problem. in the event viewer we get an error with the event ID: 5983. i did a google on it and find a solution for the old 3.0 version(which was to update to a later 3.0 version). we are on 4.0 with the latest updates. thanks for your help Hi, There is a problem attaching an email which has an attachment with an '&' in the file name. Could this be your problem? Rob "Tyler Ba...

Tables in Publisher #3
I am trying to figure out how to get tables in Publisher to auto create a new table on a new page as the table information reaches the end of a page. Auto build the tables. It is possible to do this in Word but I can not seem to find out how to get it to work in Publisher. -- Jeremie B You can't. Publisher recognizes a page, Word doesn't so it just goes on and on. Tables are one of the few areas that Word has the edge over Publisher (but I'm biased). -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Jer...

Crm grid error
I've created a custom entity called Billed hours and associated it to the case entity (which is the parent). It doesn't allow me to open the instance of the billed hours entity in the associated view and when i select it and try to delete it, it says: "You must select one or more records before you can perform this action". I'd appreciate any help :) ...

CSocket problem #3
How can I use the same CSocket instance to Send and Receive data in different threads? I tried this but run-time error occurs. Alex wrote: > How can I use the same CSocket instance to Send and Receive data in > different threads? > I tried this but run-time error occurs. > > You can't do that. CSocket uses the message pump of the thread in which it was created. Each thread has a different message pump. The same is true for CAsyncSocket, but CAsyncSocket can send and receive concurrently using only one thread. Because it is nonblocking and message-driven you can...

Upgrading to CRM 3.0 Problems
Hello, We're upgrading from CRM 1.2 to 3.0 and are coming across an error we can't get past. After entering all the information it brings up the system requirements screen where it goes through everything to make sure the install will be smooth. Everything checks out except for IIS. The exact error is "The Web site's NTAuthenticationProviders must be Negotiate,NTLM" We've researched the error on the web and found the adminscripts command to make the site Negotiate,NTLM and the get command does show it to be just that but when we restart IIS and start the ins...

ACT to MS CRM 3.x Migration using Scribe Insight
Hi, I have installed Scribe Insight and Scribe Adapter for Microsoft CRM (trial version for now). I can't seem to find a nice step by step document to on how to use this software. First of all, does ACT need to be installed on the server where CRM and Scribe are running or can it just use an ACT file? Has anyone used Scribe successfully without having taken the training? Is the training recommended? mscrmuser wrote: > Hi, > > I have installed Scribe Insight and Scribe Adapter for Microsoft CRM > (trial version for now). I can't seem to find a nice step by step > ...

10 random numbers from 0 to 20
Hello, how can I write a method that returns me 10 random numbers from 0 to 20 (included), without repetitions? Thanks a lot. Luigi On 15-06-2010 12:10, Luigi wrote: > Hello, > how can I write a method that returns me 10 random numbers from 0 to 20= > (included), without repetitions? If you don't want repetitions they are not real random numbers. You could get the number '15' 10 times in a row, it is possible, but=20 highly unlikely. --=20 Med venlig hilsen / Best regards S=C3=B8ren Reinke, IDCS #505926, TDI Trimix www.Dykkeren.dk Dive bl...