Two questions
First, I need to insert the dbid into the table so I can cross
reference the log files with other data. That being said, I can't
seem to get the update field to work properly. As always, it is most
likely something pretty obvious that I am missing.
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
command returns the entire spaced used by all the log files attached to
that database. is there a command that will break it into the component
parts?
code:
/ ****************************************
**********
Script to calculate information about the Log Files
****************************************
**********/
CREATE TABLE #dbcc_sqlperf (
DB_Name varchar(50),
Log_Size decimal (28, 5),
Log_Used_Percent decimal (28, 5),
Status tinyint )
CREATE TABLE #logstats (
DBID tinyint,
DB_Name varchar(50),
Log_Total_Size_in_MB decimal (28, 2),
Log_Used_Size_in_MB decimal (28, 2),
Log_Free_Size_in_MB decimal (28, 2),
Log_Percent_Used decimal (28, 2))
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
SELECT * FROM #dbcc_sqlperf --Debug
INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
SELECT DB_Name = DB_Name,
Log_Total_Size_in_MB = log_size,
Log_Used_Size_in_MB = sum
(log_size*(log_used_percent/100)),
Log_Free_Size_in_MB = sum (log_size
-(log_size*(log_used_percent/100))),
Log_Percent_Used = log_used_percent
FROM #dbcc_sqlperf
GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
update #logstats (DBID)
Select dbid = DBID
from master..sysdatabases where name = #logstats.DB_Name
SELECT * FROM #logstats --Debug
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf
Mattew
update #logstats set DBID=(select DBID
from master..sysdatabases where name = #logstats.DB_Name)
where exists (select * from master..sysdatabases where name =
#logstats.DB_Name)
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1143043015.896576.256210@.v46g2000cwv.googlegroups.com...
>
Two questions
>
>
First, I need to insert the dbid into the table so I can cross
>
reference the log files with other data. That being said, I can't
>
seem to get the update field to work properly. As always, it is most
>
likely something pretty obvious that I am missing.
>
>
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
>
command returns the entire spaced used by all the log files attached to
>
that database. is there a command that will break it into the component
>
parts?
>
>
code:
>
/ ****************************************
**********
>
Script to calculate information about the Log Files
>
****************************************
**********/
>
>
CREATE TABLE #dbcc_sqlperf (
>
DB_Name varchar(50),
>
Log_Size decimal (28, 5),
>
Log_Used_Percent decimal (28, 5),
>
Status tinyint )
>
>
CREATE TABLE #logstats (
>
DBID tinyint,
>
DB_Name varchar(50),
>
Log_Total_Size_in_MB decimal (28, 2),
>
Log_Used_Size_in_MB decimal (28, 2),
>
Log_Free_Size_in_MB decimal (28, 2),
>
Log_Percent_Used decimal (28, 2))
>
>
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
>
>
SELECT * FROM #dbcc_sqlperf --Debug
>
>
INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
>
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
>
SELECT DB_Name = DB_Name,
>
Log_Total_Size_in_MB = log_size,
>
Log_Used_Size_in_MB = sum
>
(log_size*(log_used_percent/100)),
>
Log_Free_Size_in_MB = sum (log_size
>
-(log_size*(log_used_percent/100))),
>
Log_Percent_Used = log_used_percent
>
FROM #dbcc_sqlperf
>
GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
>
>
update #logstats (DBID)
>
Select dbid = DBID
>
from master..sysdatabases where name = #logstats.DB_Name
>
>
SELECT * FROM #logstats --Debug
>
>
DROP TABLE #logstats
>
DROP TABLE #dbcc_sqlperf
>
>
