database size is very large

Hi All,
I ran sp_spaceused and the result is following:
database_name database_size unallocated space
DB1 6462.50 MB 1466.20 MB
reserved data index_size unused
4108216 KB 1889512 KB 298952 KB 1919752 KB
sp_helpfile gives me the following:
name size maxsize growth
DB1_dat 5609600 KB Unlimited 10%
DB1_log 1008000 KB Unlimited 10%
I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
the same results.
I think the db size is much more than it is.
Could you please tell what the above results tell me and what to do?
Thanks a million in advance.
Best regards,
mamunHi
Probably you did not perfom BACKUP LOG for a while and your database is set
to FULL/BULK-LOGGED recovery mode .
Try to set recovery mode of the database to SIMPLE or BACKUP LOG and the
run DBCC SHRINKFILE .
For more details please refer to the BOL.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
> Hi All,
> I ran sp_spaceused and the result is following:
> database_name database_size unallocated space
> DB1 6462.50 MB 1466.20 MB
> reserved data index_size unused
> 4108216 KB 1889512 KB 298952 KB 1919752 KB
> sp_helpfile gives me the following:
> name size maxsize growth
> DB1_dat 5609600 KB Unlimited 10%
> DB1_log 1008000 KB Unlimited 10%
>
> I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
> the same results.
> I think the db size is much more than it is.
> Could you please tell what the above results tell me and what to do?
> Thanks a million in advance.
> Best regards,
> mamun
>|||The transaction log accumulates data until a backup of the database is
performed or the log is truncated.
You will also want to perform DBCC SHOWCONTIG to investigate the amount
of data/index fragmentation and DBCC INDEXDEFRAG to resolve it.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
> Hi All,
> I ran sp_spaceused and the result is following:
> database_name database_size unallocated space
> DB1 6462.50 MB 1466.20 MB
> reserved data index_size unused
> 4108216 KB 1889512 KB 298952 KB 1919752 KB
> sp_helpfile gives me the following:
> name size maxsize growth
> DB1_dat 5609600 KB Unlimited 10%
> DB1_log 1008000 KB Unlimited 10%
>
> I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
> the same results.
> I think the db size is much more than it is.
> Could you please tell what the above results tell me and what to do?
> Thanks a million in advance.
> Best regards,
> mamun
>|||Just to avoid confusion:

> The transaction log accumulates data until a backup of the database is
performed or the log is
> truncated.
I'd like to phase above as:
The transaction log is truncated then a transaction log backup is performed
(or regularly when SQL
Server performs checkpoint if db in simple recovery mode). The log is not tr
uncated when a database
backup is performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JT" <someone@.microsoft.com> wrote in message news:ufwTbnZzFHA.4032@.TK2MSFTNGP15.phx.gbl...

> The transaction log accumulates data until a backup of the database is
performed or the log is
> truncated.
> You will also want to perform DBCC SHOWCONTIG to investigate the amount
of data/index
> fragmentation and DBCC INDEXDEFRAG to resolve it.
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> "microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in mes
sage
> news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
>|||Thanks a lot everyone for your suggestions. That helped.
Best regards,
mamun

Database Properties

Hi,
From where can I get SpaceAvailableInMB and KB for a database for SQL
2000 and SQL 2005.
sp_spaceused gives the Size of the Database, Data Space Used, Index
Space Used.
SQL-DMO gives it.
Thanks
Prasad
Hi
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eWfha44MGHA.2644@.TK2MSFTNGP10.phx.gbl...
> Hi,
> From where can I get SpaceAvailableInMB and KB for a database for SQL
> 2000 and SQL 2005.
> sp_spaceused gives the Size of the Database, Data Space Used, Index
> Space Used.
> SQL-DMO gives it.
> Thanks
> Prasad
>

Database Properties

Hi,
From where can I get SpaceAvailableInMB and KB for a database for SQL
2000 and SQL 2005.
sp_spaceused gives the Size of the Database, Data Space Used, Index
Space Used.
SQL-DMO gives it.
Thanks
PraHi, Pra
It seems that your goal is to re-write SQL-DMO using T-SQL/ADO. If
that's the case, your job will be easier if you use Profiler to see
what SQL-DMO is doing. For example, execute the following VB code while
Profiler is running:
Dim s As New SQLServer, d As Database
s.LoginSecure = True
s.Connect
Set d = s.Databases("SomeDatabase")
Debug.Print d.SpaceAvailableInMB
You will see that the last statements executed by SQL-DMO are:
exec sp_spaceused
DBCC SQLPERF(LOGSPACE)
In this scenario, it seems that the SpaceAvailableInMB is computed in
the following way:
SpaceAvailableInMB = "unallocated space"
+ "Log Size (MB)" / 1.024 * (1 - "Log Space Used (%)" / 100)
(some roundings may occur, since the documentation states that the
figure is accurate to two decimal places).
Razvan|||Thanks Razvan once again !!!!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140165780.578007.279570@.g47g2000cwa.googlegroups.com...
> Hi, Pra
> It seems that your goal is to re-write SQL-DMO using T-SQL/ADO. If
> that's the case, your job will be easier if you use Profiler to see
> what SQL-DMO is doing. For example, execute the following VB code while
> Profiler is running:
> Dim s As New SQLServer, d As Database
> s.LoginSecure = True
> s.Connect
> Set d = s.Databases("SomeDatabase")
> Debug.Print d.SpaceAvailableInMB
> You will see that the last statements executed by SQL-DMO are:
> exec sp_spaceused
> DBCC SQLPERF(LOGSPACE)
> In this scenario, it seems that the SpaceAvailableInMB is computed in
> the following way:
> SpaceAvailableInMB = "unallocated space"
> + "Log Size (MB)" / 1.024 * (1 - "Log Space Used (%)" / 100)
> (some roundings may occur, since the documentation states that the
> figure is accurate to two decimal places).
> Razvan
>

Database Properties

Hi,
From where can I get SpaceAvailableInMB and KB for a database for SQL
2000 and SQL 2005.
sp_spaceused gives the Size of the Database, Data Space Used, Index
Space Used.
SQL-DMO gives it.
Thanks
PrasadHi
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eWfha44MGHA.2644@.TK2MSFTNGP10.phx.gbl...
> Hi,
> From where can I get SpaceAvailableInMB and KB for a database for SQL
> 2000 and SQL 2005.
> sp_spaceused gives the Size of the Database, Data Space Used, Index
> Space Used.
> SQL-DMO gives it.
> Thanks
> Prasad
>

Database Properties

Hi,
From where can I get SpaceAvailableInMB and KB for a database for SQL
2000 and SQL 2005.
sp_spaceused gives the Size of the Database, Data Space Used, Index
Space Used.
SQL-DMO gives it.
Thanks
PrasadHi
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eWfha44MGHA.2644@.TK2MSFTNGP10.phx.gbl...
> Hi,
> From where can I get SpaceAvailableInMB and KB for a database for SQL
> 2000 and SQL 2005.
> sp_spaceused gives the Size of the Database, Data Space Used, Index
> Space Used.
> SQL-DMO gives it.
> Thanks
> Prasad
>