SP_Showing log size for all DB's

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

Transact-SQL (2000)

SP_Showing log size for all DB's

Author

Topic

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 02:55:52

Annyone know one ?

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 03:20:18

Found a way using

sys.master_files

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 03:51:22

NEW question

how do I do this ?

set @DBName = @DBName+'string'

its inside the same cursor but I want to add something more to the text in the end but the string dosent get added to the variable wenn I use print... annyone have an idea ?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 04:15:00

Can U post your code..?

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 04:20:48

My full code is for now

and acturaly I have 2 problem but only posted one.. as number 2 is proberly trivial

USE [LagerTilFlow]
GO

CREATE TABLE IsBackupNeeded (
DBName char(128) PRIMARY KEY,
LogningMode char(128) NULL,
LogSize char(128) NULL)

INSERT INTO IsBackupNeeded (DBName)
SELECT name FROM master.dbo.sysdatabases
/* Hvis der skal ekskluderes en eller flere Db'er
WHERE name NOT IN ('')
*/
DECLARE @DBName char (128)

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT DBName from IsBackupNeeded
ORDER by DBName

OPEN dcur

FETCH NEXT FROM dcur INTO @DBName

WHILE @@FETCH_STATUS=0
BEGIN

UPDATE IsBackupNeeded
SET LogningMode = (CONVERT(char(128), databasepropertyex(@DBName,'recovery')))
WHERE DBName = @DBName

set @DBName = @dbname+'string' <------- PROBLEM START AND END

print @DBName

SELECT size FROM sys.master_files <---- PROBLEM start
UPDATE IsBackupNeeded
SET LogSize
WHERE name = @DBName <----- PROBLEM end

FETCH NEXT FROM dcur INTO @DBName

END

CLOSE dcur
DEALLOCATE dcur
go

drop TABLE IsBackupNeeded

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 04:28:42

what is the error u have faced

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 04:42:39

set @DBName = @DBName+'string'

Does not add the +'string' to the variable

and

SELECT size FROM sys.master_files
UPDATE IsBackupNeeded
SET LogSize
WHERE name = @DBName

gives me the following
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 04:56:09

is sys.master_files is created by yourself. If Yes please write like

UPDATE tbl1
SET LogSize( SELECT size FROM sys.master_files tbl2
Where tbl.key_value = tbl2.keyvalue)
From IsBackupNeeded tbl1
WHERE name = @DBName

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 04:58:22

Sorry
SET LogSize = ( SELECT size FROM sys.master_files tbl2

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 05:01:29

You have two options:

1. Change the data type of @DBName to varchar(128)
2. set @DBName = ltrim(rtrim(@dbname))+'string'

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 05:06:19

quote:
Originally posted by bkpgamble

SELECT size FROM sys.master_files
UPDATE IsBackupNeeded
SET LogSize
WHERE name = @DBName

gives me the following
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.

There are two errors here:

1. After selecting size column from sys.master_files, you haven't assigned it to any variable
2. In UPDATE statement, you haven't provided value for size column of IsBackupNeeded table.

Do this:

UPDATE IsBackupNeeded
SET LogSize = (SELECT size FROM sys.master_files where name = @DBName)
WHERE name = @DBName

UPDATE b
SET LogSize = m.size
From IsBackupNeeded b Join Sys.master_files m
on b.name = m.name
WHERE b.name = @DBName

Harsh Athalye
India.
"Nothing is Impossible"

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-06 : 05:06:25

What version of SQL Server ?

dbcc sqlperf(logspace)

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 05:14:25

If you are trying to automate the backup of all the databases on your server see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Automating%20Backups,Backup

Kristen

bkpgamble
Starting Member

30 Posts

Posted - 2006-10-06 : 05:18:30

Thanks all for the response :) I have fixed it now

and Thanks for the link will provide some good reading

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources