DBCC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-26 : 15:02:59

Is this the possible way for me to do some thing like this

when I Exec: dbcc sqlperf(logspace) I got all the information for DatabaseName, LogSize, LogSpaceUsed, Status for the entire server.
I created the datbase Name DB1 to have these fields. How can I get all the information when I exec bcc sqlperf(logspace) then insert into DB1.

This is the SP I created for DB1:
CREATE PROCEDURE [insert_SpaceLogAvailable]
(@ID [int],
@DBName [varchar](255),
@LogSize_MB [varchar](255),
@LogSpaceUsed [varchar](255))

AS INSERT INTO [Mis].[dbo].[SpaceLogAvailable]
( [ID],
[DBName],
[LogSize(MB)],
[LogSpaceUsed%])

VALUES
( @ID,
@DBName,
@LogSize_MB,
@LogSpaceUsed)
GO

Edited by - vicki on 02/26/2002 15:07:41

izaltsman
A custom title

1139 Posts

Posted - 2002-02-26 : 16:06:53

The structure of the table you are inserting into has to be exactly the same as the structure of the DBCC output. Once you create this table, use INSERT... EXEC(...) construct to populate it.


create table #dbccresults
(
DBName [varchar](255),
LogSize_MB decimal(18, 8),
LogSpaceUsed decimal(18, 8),
status int)

insert into #dbccresults
exec ('dbcc sqlperf(logspace)')

select * from #dbccresults

Edited by - izaltsman on 02/26/2002 16:07:37

donnaj
Starting Member

3 Posts

Posted - 2002-02-26 : 16:21:36

hi,

try this:

create proc testing123 as
dbcc sqlperf(logspace)
go
grant execute on testing123 to public

this proc above does the dbcc for ya

this stuff below will load the table, you can make this a proc also, if ya want.

create table #temp1(dbname varchar(64) null,lgsize numeric(18,8) null,lgsu numeric(18,8) null,sts integer null)

declare @sql varchar(1000)

select @sql = 'insert into #temp1 execute testing123'

execute(@sql)

select * from #temp1

drop table #temp1

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-26 : 17:16:43

Thanks,
ok It work now, however, I want to display these information on the page and how can I get the update information?
if I re-run the query again, or I have that query on the webpage , hen I have the duplicate record which mean that was insert over and over again everytime I run the query?. Is there any way to avoid this? which mean you just have the current update information in the datatabase even how many time you run the query?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-26 : 17:31:20

What exactly do you want to do? Do you want live updates (or ad hoc queries) on the log space on your server(s)? If so, you can simply run DBCC SQLPERF(LOGSPACE) without having to keep the information in a table at all, unless you need to keep these results for tracking purposes. Naturally you will get "dupes" unless you add a column that identifies when the query was run. You can simply add a datetime column to the log space table you're INSERTing into that has a default value of GetDate().

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-27 : 08:56:37

Thanks,

What I try to do is after I run DBCC SQLPERF(LOGSPACE) then I got all the information then I insert these information into the new table I just created it. This step is working. however, next time, I come back and run DBCC SQLPERF(LOGSPACE) again, then I have another information that Insert into that table. My question is there any way for me to do is give me the update one which is mean give me the information for the last time I run DBcc.

For example, I have 20 databases in Server , today I run DBCC then got all the information for that 20 DBs that insert into my table, but then to morrow I run DBCC again , then I also have another 20 information insert into database. Do you all know any way for me to delete the last 20 information that I run yesterday and relplace with current information?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-27 : 09:02:55

You can simply DELETE FROM logTable and then peform the INSERT...EXECUTE to repopulate it. Add the DELETE statement to your existing stored procedure.

Nazim
A custom title

1408 Posts

Posted - 2002-02-27 : 09:02:56

before running your inserts you can delete all the previous records of your table. either by using

Delete from tablename
or
truncate table tablename

i would suggest you to create a stored procedure which does delete's first and then inserts. thereby leaving you with only the lastest information .

HTH

--------------------------------------------------------------

Edited by - Nazim on 02/27/2002 09:07:13

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-27 : 09:03:34

NEW RECORD! A one-second snipe!

Nazim
A custom title

1408 Posts

Posted - 2002-02-27 : 09:06:16

Ah! you are Ahead ROB !

BTW, What are the other reconds on Sqlteam.

We should hire a statistician to keep track of all the activities and Give awards on every records

--------------------------------------------------------------

Edited by - Nazim on 02/27/2002 09:11:32

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-27 : 09:10:50

Thanks,

I did think that way. However, these information was display on the web page for some one to view it. But if he refresh the brower then it automate insert the new information. How can I prent this situation?

Thanks

Nazim
A custom title

1408 Posts

Posted - 2002-02-27 : 09:14:28

Are you using Asp.NEt or Asp.

Am not sure about Asp. but if you are using ASP.Net

check for Page.IsPostBack event and play with it.

HTH

--------------------------------------------------------------

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-27 : 09:16:22

I am using Coldfusion
--------------------------------------------------------------

[/quote]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-27 : 09:35:22

First, you need to decide if this should reflect the most up-to-the-minute results, or if this is some kind of scheduled procedure. If you need the absolute latest information, then don't bother with intermediate table, just run DBCC SQLPERF(LOGSPACE) each time they hit the page. If it's a once-a-day/once-an-hour/once-every-10-minutes kind of thing, then create a job to run the stored procedure that clears the table and re-inserts the information, then set an appropriate schedule for it. The web users will simply be querying that table, and never run DBCC SQLPERF(LOGSPACE).

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-27 : 10:30:41

Thanks so much for all of your input.

I have this in the store procedure then refrence the procedure into my page then i worked fine

CREATE PROCEDURE GetLogResult
AS
Truncate table dbccresults
insert into dbccresults exec ('dbcc sqlperf(logspace)')select * from dbccresults
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources