Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with sp_MsForEachDb


help with sp_MsForEachDb

Author
Message
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
Hi everyone. I have created a table using the following code in sql2008:
USE Dba_Admin
GO
CREATE TABLE dbo.databaseDataFileSize (
rowId INT IDENTITY(1,1),
dbName sysname,
dbSize BIGINT,
sampleDate DATE,
sampleTime TIME)
GO




I am trying to alter a script to run which should populate the table using the Sp_MsforeachDb. I am running the follwoing code to try this :

EXEC master.sys.sp_MSforeachdb 
INSERT INTO Dba_admin.dbo.databaseDataFileSize
SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time)
FROM [?].sys.database_files
WHERE type = 0;



but i am getting the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '?'.


Can anybody help me modify this so it itterates through each database and populates the table so i can keep track of my datafile sizes ?
Thanks
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
I wonder if you're missing the quote marks or if it was a copy/paste error.


EXEC master.sys.sp_MSforeachdb
N'INSERT INTO Dba_admin.dbo.databaseDataFileSize
SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time)
FROM [?].sys.database_files
WHERE type = 0;'



PS. Depending on your database sizes, you might want to cast your size column as bigint.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
If you like sp_msforeachdb you should read this:

A more reliable and more flexible sp_MSforeachdb

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36172 Visits: 18751
You can't use ms_foreachdb to insert a parameter into a query that's directly executed. You need to submit a query as a parameter.

Luis has shown you how this would work.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
guys - thanks for all the help. @Luis you where correct - i was just missing the quotes !! It was a late night and sometimes you just cant see the wood for the trees !!

@Steve - Thanks for the extra info on this. By the way - terrible shirt at the sql in the city in London !!!! ;-)
Fantastic day though !!
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36172 Visits: 18751
PearlJammer1 (7/4/2013)

@Steve - Thanks for the extra info on this. By the way - terrible shirt at the sql in the city in London !!!! ;-)
Fantastic day though !!


you misspelled l-o-v-e-l-y

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1908 Visits: 10946
I think you might be able to get this work with SP_MSForEachDB still. I've got a script to get a list of all DBs and any orphaned users in them. To make it easier to read, it includes the name of the DB by saving the DB name as a variable.

So possibly with your code this:
EXEC master.sys.sp_MSforeachdb 
N'
declare @dbname sysname
set @dbname = ''?''
INSERT INTO Dba_admin.dbo.databaseDataFileSize
SELECT @dbname, SUM(size) * 8, cast(getdate() as date), cast(getdate() as time)
FROM [?].sys.database_files
WHERE type = 0;'


might work.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
jasona.work (7/5/2013)
might work.

It might. Or it might randomly skip databases. sp_MsForEachDb is fatally flawed and although Microsoft seems happy enough to employ it within their own Maintenance Plan code, it should not really be trusted. Check out the article I linked to earlier.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1908 Visits: 10946
opc.three (7/5/2013)
jasona.work (7/5/2013)
might work.

It might. Or it might randomly skip databases. sp_MsForEachDb is fatally flawed and although Microsoft seems happy enough to employ it within their own Maintenance Plan code, it should not really be trusted. Check out the article I linked to earlier.


True. I had come across that article back when I was starting to dig into MSFForEachDB, and I do like the features he added into his version of the SP.

I guess, the second most common answer to a DBA question applies:
test, test, test in QA, then test again.
;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
jasona.work (7/5/2013)
opc.three (7/5/2013)
jasona.work (7/5/2013)
might work.

It might. Or it might randomly skip databases. sp_MsForEachDb is fatally flawed and although Microsoft seems happy enough to employ it within their own Maintenance Plan code, it should not really be trusted. Check out the article I linked to earlier.


True. I had come across that article back when I was starting to dig into MSFForEachDB, and I do like the features he added into his version of the SP.

I guess, the second most common answer to a DBA question applies:
test, test, test in QA, then test again.
;-)

The need to test is a given. However, knowing the procedure is flawed why use it? Or worse, recommend it? If you read into the blog post you'll learn that the skipping of databases is happenstance and so cannot be predictable even in a QA environment, i.e. the procedure is fatally flawed and is not really fit for production use unless accuracy is unimportant.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search