database size

Hi,
I've got a question about database size.
When I go to EM database properties I can see that database size is 4599.56
MB. Also it tells that I've got 2664,98 MB available space. I thought I can
shrink database and so return that space to OS. However after shrinking DB
the size remains the same. I tried dbcc shrinkfile as well, but no luck
exec sp_helpdb dbname
4599.56 MB
Has anybody got an idea how should I reduce the DB size?
Thanks a lot
DariusIt may be the data is stored all over the place in that 4599.56 MB
(framentation), that why despite it indicates half of them are not
actually used but when you try to shrink it, it does not work.
Try to use DBCC SHRINKFILE with the NOTRUNCATE option along with the
target size.
When NOTRUNCATE is specified along with target_size, the space freed is
not released to the operating system. The only effect of the DBCC
SHRINKFILE is to relocate used pages from above the target_size line to
the front of the file.
This should then deframent the data file and after that run the DBCC
SHRINKFILE again with the target_size. It should then do the trick.
Alternative deframentation options -
1) backup the database and restore the database.
2) use 3rd party deframentation tools (this one you will need to take
the db offline beforehand).
Mel|||Hi Mel,
Unfortunately no luck with DBCC SHRINKFILE NOTRUNCATE and DBCC SHRINKFILE,
targetsize after that.
I tried to backup and restore then. Size of database backup file is ~ 4.7 GB
and after restore the db size was still 4599.69 MB. Have tried SHRINKFILE
on restored DB, but free space still remains 2663.52 MB
Any other ideas?
Darius
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145376698.375718.152140@.i40g2000cwc.googlegroups.com...
> It may be the data is stored all over the place in that 4599.56 MB
> (framentation), that why despite it indicates half of them are not
> actually used but when you try to shrink it, it does not work.
> Try to use DBCC SHRINKFILE with the NOTRUNCATE option along with the
> target size.
> When NOTRUNCATE is specified along with target_size, the space freed is
> not released to the operating system. The only effect of the DBCC
> SHRINKFILE is to relocate used pages from above the target_size line to
> the front of the file.
> This should then deframent the data file and after that run the DBCC
> SHRINKFILE again with the target_size. It should then do the trick.
> Alternative deframentation options -
> 1) backup the database and restore the database.
> 2) use 3rd party deframentation tools (this one you will need to take
> the db offline beforehand).
> Mel
>|||Backup and restore will not reduce size for database files. Is it a data or
log file that is "too
large"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sabotage" <nospam> wrote in message news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...reen">
> Hi Mel,
> Unfortunately no luck with DBCC SHRINKFILE NOTRUNCATE and DBCC SHRINKFILE
, targetsize after that.
> I tried to backup and restore then. Size of database backup file is ~ 4.7
GB and after restore the
> db size was still 4599.69 MB. Have tried SHRINKFILE on restored DB, but
free space still
> remains 2663.52 MB
> Any other ideas?
> Darius
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1145376698.375718.152140@.i40g2000cwc.googlegroups.com...
>|||Try to backup transection logs
Or shrink logs using T-SQL script
http://www.neweb.co.il/mcse/scripts/shrinksqllogs.html
??????!
??? ????? ??? "??" ??? ?????.
???
"Tibor Karaszi" wrote:

> Backup and restore will not reduce size for database files. Is it a data o
r log file that is "too
> large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx
.gbl...
>|||It is a data file I am having problems with. Log file can be reduced.
D.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Backup and restore will not reduce size for database files. Is it a data
> or log file that is "too large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message
> news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...
>|||It is a data file I am having problems with. Log file can be reduced.
D.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Backup and restore will not reduce size for database files. Is it a data
> or log file that is "too large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message
> news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...
>|||Well, I thought the backup and restore database may work in terms of
defragmentation.
Because I used to support Oracle and one way to defragment the data
files were export the data out and import back to the same database
container. It doesn't seen be the case for MSSQL I wonder if the
restore also restore the storing pattern (how data is stored).
It may be unusual, but can you tell me how large is your model database
at that instance? The database cannot be made smaller than the size of
the model database.
I will be suprised if it is because of that. Can you post the result
set here (the one you received after the running the dbcc shrinkfile
'datafile' cmd'? It will give me more ideas.
Mel|||And you tried DBCC SHRINKFILE, not specifying either NOTRUNCATE or TRUNCATEO
NLY?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sabotage" <nospam@.dot.com> wrote in message news:z7c1g.52$4k5.29@.newsfe4-gui.ntli.net...[vb
col=seagreen]
> It is a data file I am having problems with. Log file can be reduced.
> D.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
>[/vbcol]|||Mel, Tibor,
Model database size is 1.63 MB, so there's no problem with it.
Also I have tried all the variations of DBCC SHRINKFILE, with and without
NOTRUNCATE and TRUNCATEONLY
The results of DBCC SHRINKFILE ('dbname') are:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
7 1 588632 128 588608 588608
I upload the data to the database using BULK INSERT from text file in native
format
Darius
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145395598.936282.159010@.e56g2000cwe.googlegroups.com...
> Well, I thought the backup and restore database may work in terms of
> defragmentation.
> Because I used to support Oracle and one way to defragment the data
> files were export the data out and import back to the same database
> container. It doesn't seen be the case for MSSQL I wonder if the
> restore also restore the storing pattern (how data is stored).
> It may be unusual, but can you tell me how large is your model database
> at that instance? The database cannot be made smaller than the size of
> the model database.
> I will be suprised if it is because of that. Can you post the result
> set here (the one you received after the running the dbcc shrinkfile
> 'datafile' cmd'? It will give me more ideas.
> Mel
>

Database recovery option

On right click of database in ER, goto properties and Options tab, there is
Recovery model selection, what would happen if I select one of them ?Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft® SQL Server? 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft® SQL Server? 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
>> On right click of database in ER, goto properties and Options tab,
>> there is
>> Recovery model selection, what would happen if I select one of them ?
>>|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
> > Alan,
> >
> > From Books Online:
> >
> > You can select one of three recovery models for each database in
> > Microsoft?SQL Server?2000 to determine how your data is backed up and
> > what your exposure to data loss is. The following recovery models are
> > available:
> >
> > Simple Recovery
> > Simple Recovery allows the database to be recovered to the most recent
> > backup.
> >
> > Full Recovery
> > Full Recovery allows the database to be recovered to the point of
failure.
> >
> > Bulk-Logged Recovery
> > Bulk-Logged Recovery allows bulk-logged operations.
> >
> > The recovery model of a new database is inherited from the model
> > database when the new database is created.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Alan wrote:
> >
> >> On right click of database in ER, goto properties and Options tab,
> >> there is
> >> Recovery model selection, what would happen if I select one of them ?
> >>
> >>|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>>The more recommeded model is Full Recovery. And switching between the
>>model could impact/break the continuity of your log and your overall
>>backup and recovery strategy. You might want to fully understand it
>>before you start thinking what you want to do with it.
>>
>>Mark Allison wrote:
>>Alan,
>> From Books Online:
>>You can select one of three recovery models for each database in
>>Microsoft?SQL Server?2000 to determine how your data is backed up and
>>what your exposure to data loss is. The following recovery models are
>>available:
>>Simple Recovery
>>Simple Recovery allows the database to be recovered to the most recent
>>backup.
>>Full Recovery
>>Full Recovery allows the database to be recovered to the point of
> failure.
>>Bulk-Logged Recovery
>>Bulk-Logged Recovery allows bulk-logged operations.
>>The recovery model of a new database is inherited from the model
>>database when the new database is created.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Alan wrote:
>>
>>On right click of database in ER, goto properties and Options tab,
>>there is
>>Recovery model selection, what would happen if I select one of them ?
>>
>
>|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
> > I just wonder what would happen if:
> > 1) choose full recovery mode, then create a maintenance plan, just
backup
> > the database without transaction log backup ?
> >
> > 2)choose simple recovery mode, then create a maintenance plan, backup
both
> > database and transaction log ?
> >
> > Do I still get the data back from the point of failure ?
> >
> >
> >
> > "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> > news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> >
> >>The more recommeded model is Full Recovery. And switching between the
> >>model could impact/break the continuity of your log and your overall
> >>backup and recovery strategy. You might want to fully understand it
> >>before you start thinking what you want to do with it.
> >>
> >>
> >>Mark Allison wrote:
> >>
> >>Alan,
> >>
> >> From Books Online:
> >>
> >>You can select one of three recovery models for each database in
> >>Microsoft?SQL Server?2000 to determine how your data is backed up and
> >>what your exposure to data loss is. The following recovery models are
> >>available:
> >>
> >>Simple Recovery
> >>Simple Recovery allows the database to be recovered to the most recent
> >>backup.
> >>
> >>Full Recovery
> >>Full Recovery allows the database to be recovered to the point of
> >
> > failure.
> >
> >>Bulk-Logged Recovery
> >>Bulk-Logged Recovery allows bulk-logged operations.
> >>
> >>The recovery model of a new database is inherited from the model
> >>database when the new database is created.
> >>
> >>--
> >>Mark Allison, SQL Server MVP
> >>http://www.markallison.co.uk
> >>
> >>Looking for a SQL Server replication book?
> >>http://www.nwsu.com/0974973602m.html
> >>
> >>
> >>Alan wrote:
> >>
> >>
> >>On right click of database in ER, goto properties and Options tab,
> >>there is
> >>Recovery model selection, what would happen if I select one of them ?
> >>
> >>
> >
> >
> >

Database recovery option

On right click of database in ER, goto properties and Options tab, there is
Recovery model selection, what would happen if I select one of them ?
Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft SQL Server 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>
|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:[vbcol=seagreen]
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft SQL Server 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
failure.[vbcol=seagreen]
|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>
> failure.
>
>
|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
backup[vbcol=seagreen]
both[vbcol=seagreen]

database properties show incorrect information

The backup file size for database xyz shows the size of 200,000kb.

However when I click on the property from enterprise manager, it shows 3000MB. What is the reason that the backup file shows a much smaller size than the size that showed from EM db property?

Thanks for your input!

Database files have a "reserved" space which wil give you the availbility, as in your case to put data up to 3000MB to it, before it will grow (if set up). The backup file on the other side will only backup the data not the reserved space which might be not occupied. You will need to have a look in a procedure like sp_Spaceused or the proper view in Enterprise Manager to get the occupied values.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

database properties query

I've been asked to provide some information about databases on an sql server.

I can use the following query to provide the names of all the databases:

select * from master.dbo.sysdatabases

What I'm also looking to provide is the current "size" of the database. I'm trying to find a table that I can link to that would provide the database size. I can manually find this number by right clicking on the database from enterprise manager, selecting "properties" and picking it up from the general tab.

This is sql server 2K...

Thanks.look at sysfiles for each database ... that should get you started|||may be overkill, but check out the app in my sig.

Database properties

Hi.

I am trying to look for ways on how to get all the databse properties under each instance.

say I want to view the size, recovery model, physical location, etc.

The sys.database_files is only applicable per database. Is there a query, or a table in master database to get these or store these values?

thanks...

You can query the sys.databases and sys.master_files to get this information on a per instance basis. You will need to query each instance seperatly.|||Thanks for that!

Database Properties

is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc?

Cheers

I have managed to get the database size using :

EXEC sp_MSforeachtable @.command1=EXEC sp_spaceused

|||

Hi,

Haven't tried it but you should be able to use theMicrosoft.SqlServer.Management.Smo to do so:

Here's an example :

http://www.aspfree.com/c/a/MS-SQL-Server/Retrieving-SQL-Server-2005-Database-Info-Using-SMO-Database-Info-Table-Info/1/

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

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

Is there an "sp_" already inckuded in SQL servere 2000 that will return some
properties of the database incouding users, (rights maybe?)?Check out the sp_help* stored procedures in Books Online.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"RichieRich" <RichieRich@.discussions.microsoft.com> wrote in message
news:586AD0CF-F4FE-405C-A8AB-7561BC2034E1@.microsoft.com...
> Is there an "sp_" already inckuded in SQL servere 2000 that will return
some
> properties of the database incouding users, (rights maybe?)?

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
>