How to tell the amount of space left on the SQL2005 db and the related Trans log db

I am troubleshooting space issues on my db and I needed to confirm something.  when I look at the properties of the db, I see the size of the database and the space available.   The size of the db is roughly 45Gigs and the space available is about 20Gigs.  That means that the db is using 25gigs.  How can  tell how much of it is being used by the tranaction logs?
alkammAsked:
Who is Participating?
 
Anthony PerkinsCommented:
alkamm,

Did you not see my comment?  Here it goes again:
What you are looking for is in SSMS:
1. Right click on the database.
2. Select Reports.
3. Custom Reports
4. Disk Usage.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check the size of the files individually.
0
 
mfhorizonCommented:
on your drive where sql server is installed go to
Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

and check database .ldf and .mdf file size.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tcs224694Commented:
0
 
mfhorizonCommented:
The transaction log contains every single transaction that could change data.  It uses this file to restore your database.  If you run backups regularly and then shrink your database, your transaction log should be reduced significantly.

To REDUCE your Log file size you can use LOG TRUNCATION or LOG SHRINKING. For detailed review visit http://www.akadia.com/services/sqlsrv_shrink_logfile.html
0
 
RiteshShahCommented:
well, you can check your file size from the folder which has your .MDF and .LDF file, you can truncate or shrink your transaction log but it is not good as you may need to recover sometime so the best way is to backup your transaction log.
0
 
alkammAuthor Commented:
yes I was looking at all of those things but in SQL but it is not answering my question. You can start the initial size at lets say 30Gigs and 15Gigs for the .mdf and .ldf.   That is the size that shows in the data files but that doesn't mean there is actually that much data, it is just the size that you chose the db to grow....at least I hope.  In SQL 2000 there was a graphic that would show the size of each and how much free space was left, that's what I am looking for.
0
 
RiteshShahCommented:
don't know whether this will work in SQL Server 2000
sp_spaceused --for complete picture
sp_spaceused 'emps' --give table name as I did 'emps' and you will get table size.

Open in new window

0
 
Anthony PerkinsCommented:
>> In SQL 2000 there was a graphic that would show the size of each and how much free space was left, that's what I am looking for.<<
What you are looking for is in SSMS:
1. Right click on the database.
2. Select Reports.
3. Custom Reports
4. Disk Usage.
0
 
RiteshShahCommented:
acperkins: there is no SSMS, Author has SQL 2000. :)
0
 
RiteshShahCommented:
sorry, ignore my previous post.
0
 
alkammAuthor Commented:
Hi Riteshshah,

I made a reference to SQL 2000 but I am asking about SQL 2005.  I used your sp_spaceused query and it is helpful but it doesn't separate the data and transaction log sizes.  Also that query returns a data column, is that supposed to be the actual amount of data in the db.

Thanks
0
 
RiteshShahCommented:
>> Also that query returns a data column, is that supposed to be the actual amount of data in the db.<<

yes, it shows you actual data in database excluding index spaces.
0
 
RiteshShahCommented:
have a look at this query for check size and details of .LDF and .MDF

select * from sys.sysfiles
0
 
Anthony PerkinsCommented:
Using that report it gives you all the detail you need and you don't even need to read it there is a colored chart that shows the percentage used, allocated, free.
0
 
alkammAuthor Commented:
Is that SQL 2005?
0
 
Anthony PerkinsCommented:
Yes.  SSMS is only found in SQL Server 2005 and 2008.  Prior to that it was called Enterprise Manager.
0
 
alkammAuthor Commented:
that is exactly was I was trying to find except disk usage is in the standard report section, not the custom section.

Thanks a lot
0
 
Anthony PerkinsCommented:
>>that is exactly was I was trying to find except disk usage is in the standard report section, not the custom section.<<
Absolutely.  My mistake.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.