Please help me in calculating user database size in Sybase ASE 12.5.3.
If there are any scripts please pass me those.
Thanks a lot..
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks Leon. But this tells me the estimation of db size. I need to find out total allocated(used) space and unallocated(free) space in my current database and devices from some system tables. I can very well look at the Sybase central and find out but I need to get this information from a stored procedure and pass it to the application.
Ok, vinit_kain has given you the right answer, so I'm just adding details because I can't stand to leave some myths or mistakes unresponded to. 8->
Firstly, sp_helpdb can be *very* inaccurate with its sizing. It calls sp_helpsegment, and the data it gets its information from is *not* refreshed transactionally. I've seen sp_helpdb report negative space used, or that a database was bigger than the size it was created with.
sp_spaceused is designed to be run against a single table, but if you run it without any parameters at all it will report instead on the current database. It is accurate to the extent level, ie. +/- 16Kb. It is fairly slow to run.
The ultimate answer is dbcc checkdb, which provides an exact page count, so it will be accurate to +/- 2kb (on a 2Kb page size server).
Anyway - you've already been given the right answer, which was sp_spaceused.
Dbcc checkdb is the slowest possible choice though. Most times you can live with +/- 16Kb, so sp_spaceused was definitely the right answer.
But when you absolutely positively have to have the most accurate answer, dbcc is the way to go. And it's not like it's a bad thing to dbcc your databases, right? 8->
Thanks Vinit and Thanks Joe. But how do I use these in stored procedure and get the output into variable because I need to get the ouput and store it in a table. I know I get the result set at command prompt but I do not know how to get into variable so that I can use it. Like I want to call this into my own stored procedure and get the result into output parameter and I need only result that is ununsed space for e.g. how do I do that?
My biggest problem is that when I do backup (database dump) using dump database command, I get the result like 32K bytes data dumped on command promt and I need this dump size to be stored in my backup history table. Can you please help me with this..
-PC
Usually for this sort of thing I do most of the work in UNIX shell scripts, capture the output of some SQL or stored procedures in shell variables, and use those variables for anything I have to put back in the database. It's a bit roundabout but it's very simple and it always works.
If you want a 100% SQL solution, then probably the best answer is to use sp_spaceused, create a fake server with sp_addserver to point back to the current server (you'll need two server names in your interfaces file pointing to the same address), and then setup a proxy table to a RPC (remote procedure call).
The table will allow you to see the results of the procedure as a result set. I'm not quite sure how it works as I've only rarely gone down this path because of how complex it is. However the built-in "MDA tables" do something similar - have a look at the "installmontables" script for some ideas on how this works.
It's a lot simpler to do the work at the O/S level. 8->
I am agree with Joe. I never saved these information on sybase server back
on Unix --
isql -S$server_name -U$user_name -P$user_password -D$database_name -o /tmp/space_report <<!
sp_spaceused
go
!
cat /tmp/space_report|tail -2|head -1|awk '{print "Unused space="$7/1024" MB" }'
but if you want it on sybase then
extract ddl of sp_spaceused (from sybsystemprocs database )
sp_helptext sp_spaceused.
if you have dbartisan then it will be better.
you can change this procedure by sending the output to a table instead of screen. and save this procedure in your database as user procedure.
Vinit
Thank Vinit and thanks Joe for all your answers. I guess I will try one of these options but now I have one basic doubt why Dump file size does not match with sp_spaceused stored procedure output. What I am trying to ask is what will be the dump file size from a output of sp_spaceused stored procedure.
Prakash, we're getting close to the point where this should be asked as a new question.
Briefly, sp_spaceused and messages during dump database are reporting on different things.
Sp_spaceused tells you how much space has been consumed in the database, ie. how much has been allocated to objects. Assuming a 2Kb page size, if you create an empty table then sp_spaceused will show 16Kb (8 x 2Kb pages) is now "reserved" and unavailable for other objects. If you create a clustered index and one nonclustered index on this empty table, the indexes also need their own minimum allocation of 8 pages, so even though there are no rows in your table, sp_spaceused will show 48Kb "reserved" (but probably only 2Kb "used" for data and 4Kb for indexes).
Dump database on the other hand tells you how much it wrote during the backup. Firstly this is proportional to how much data is actually used rather than just allocated. In our example above, even though the empty table consumes 48Kb only 6Kb will be written during the dump.
However it's a bit more complicated than that as pages can be written more than once during a dump.
Basically there is little or no relationship between the sizes reported by sp_spaceused and dump database because they're reporting on completely different things.
If all you need is the dump file size, then I don't think there is any way of getting that in a purely SQL solution inside ASE. You'll have to go out to the operating system and either parse the Backup Server errorlog, or just ask for the file size of the dump file(s).
Business Accounts
Answer for Membership
by: leonstrykerPosted on 2006-01-20 at 06:19:46ID: 15748876
Take a look here:
http://expertanswercenter.
Leon