Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help with sp_MsForEachDb Expand / Collapse
Author
Message
Posted Tuesday, July 02, 2013 5:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:11 AM
Points: 221, Visits: 860
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

Post #1469795
Posted Tuesday, July 02, 2013 6:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1469812
Posted Wednesday, July 03, 2013 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1470107
Posted Wednesday, July 03, 2013 9:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #1470109
Posted Thursday, July 04, 2013 2:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:11 AM
Points: 221, Visits: 860
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 !!
Post #1470348
Posted Friday, July 05, 2013 8:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #1470751
Posted Friday, July 05, 2013 8:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 906, Visits: 4,455
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.
Post #1470761
Posted Friday, July 05, 2013 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1470799
Posted Friday, July 05, 2013 9:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 906, Visits: 4,455
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.
Post #1470800
Posted Friday, July 05, 2013 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1470802
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse