DBfile usage

Hi,
I have a database that is currently 8Gb. I run some tools (like Log PI) and
tells me that the usage of my files is 25%, around 2Gb. The rest is free.
How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and DBCC
SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that the
GUI maintenance palns and jobs are almost useless. Any help?
regardsHi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Make the database single user
Alter database <dbname> set single_user with rollback immediate
3. Now shrink the files
dbcc shrinkfile('logical_mdf_name',size)
4. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',size)
5. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
6. Make the database multi user
Alter database <dbname> set multi_user
--
Thanks
Hari
MCDBA
"dimitris" <dimitris@.microsoft.com> wrote in message
news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I have a database that is currently 8Gb. I run some tools (like Log PI)
and
> tells me that the usage of my files is 25%, around 2Gb. The rest is free.
> How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and
DBCC
> SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that the
> GUI maintenance palns and jobs are almost useless. Any help?
> regards
>|||thanks Harri,
this is how my db looks like. Should I proceed with your recommendations?
DB_Name Db_size unallocated space
Axapta 9753.19 MB 1142.38 MB
reserved data index size unused
7521080 KB 2809752 KB 4593024 KB 118304 KB
and the log space is:
logsize log space used
Axapta 1265.9922 0.87000221 0
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eStdWmKZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Make the database single user
> Alter database <dbname> set single_user with rollback immediate
> 3. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',size)
> 4. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',size)
> 5. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> 6. Make the database multi user
>
> Alter database <dbname> set multi_user
> --
> Thanks
> Hari
> MCDBA
>
> "dimitris" <dimitris@.microsoft.com> wrote in message
> news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> > Hi,
> > I have a database that is currently 8Gb. I run some tools (like Log PI)
> and
> > tells me that the usage of my files is 25%, around 2Gb. The rest is
free.
> > How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and
> DBCC
> > SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that
the
> > GUI maintenance palns and jobs are almost useless. Any help?
> >
> > regards
> >
> >
>|||Hi,
Yes go ahead. Take a full database backup before doing the activity.
Command to backup"-
Backup database <dbname> to disk='d:\backup\dbname.bak' with init
--
Thanks
Hari
MCDBA
"dimitris" <dimitris@.microsoft.com> wrote in message
news:#Ela4ELZEHA.2408@.tk2msftngp13.phx.gbl...
> thanks Harri,
> this is how my db looks like. Should I proceed with your recommendations?
> DB_Name Db_size unallocated space
> Axapta 9753.19 MB 1142.38 MB
> reserved data index size unused
> 7521080 KB 2809752 KB 4593024 KB 118304 KB
> and the log space is:
> logsize log space used
> Axapta 1265.9922 0.87000221 0
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eStdWmKZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Use the below command to get the actual free space:-
> >
> > For Data and Index
> >
> > use dbname
> > go
> > sp_spaceused @.updateusage='true'
> >
> > For Transaction log
> >
> > DBCC SQLPERF(LOGSPACE)
> >
> > Based on the outcome you can shrink the MDF and LDF file seperately.
> >
> > Steps:-
> >
> > 1. Backup the transaction log (Backup Log in books online)
> > 2. Make the database single user
> >
> > Alter database <dbname> set single_user with rollback immediate
> >
> > 3. Now shrink the files
> >
> > dbcc shrinkfile('logical_mdf_name',size)
> >
> > 4. Shrink the LDF file
> >
> > dbcc shrinkfile('logical_ldf_name',size)
> >
> > 5. After this check the size again
> >
> > use dbname
> > go
> > sp_spaceused @.updateusage='true'
> >
> > For Transaction log
> >
> > DBCC SQLPERF(LOGSPACE)
> >
> > 6. Make the database multi user
> >
> >
> > Alter database <dbname> set multi_user
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "dimitris" <dimitris@.microsoft.com> wrote in message
> > news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > I have a database that is currently 8Gb. I run some tools (like Log
PI)
> > and
> > > tells me that the usage of my files is 25%, around 2Gb. The rest is
> free.
> > > How can I truncate or shrink the files/tables whatever? INDEXDEFRAG
and
> > DBCC
> > > SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that
> the
> > > GUI maintenance palns and jobs are almost useless. Any help?
> > >
> > > regards
> > >
> > >
> >
> >
>