?
Solved

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

Posted on 2009-04-24
19
Medium Priority
?
413 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:alkamm
  • 6
  • 5
  • 4
  • +3
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24223704
check the size of the files individually.
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24223741
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
 
LVL 2

Expert Comment

by:tcs224694
ID: 24223747
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:mfhorizon
ID: 24223763
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24223826
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
 

Author Comment

by:alkamm
ID: 24224939
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24225060
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24229891
>> 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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24229962
acperkins: there is no SSMS, Author has SQL 2000. :)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24229966
sorry, ignore my previous post.
0
 

Author Comment

by:alkamm
ID: 24239484
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24239498
>> 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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24239505
have a look at this query for check size and details of .LDF and .MDF

select * from sys.sysfiles
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24243925
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24243942
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
 

Author Comment

by:alkamm
ID: 24263343
Is that SQL 2005?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24265237
Yes.  SSMS is only found in SQL Server 2005 and 2008.  Prior to that it was called Enterprise Manager.
0
 

Author Closing Comment

by:alkamm
ID: 31574174
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24271968
>>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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question