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 2, 2013 5:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 255, Visits: 1,009
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 2, 2013 6:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 3,902, Visits: 8,834
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.
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?

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:23 PM
Points: 7,135, Visits: 12,743
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
Post #1470107
Posted Wednesday, July 3, 2013 9:15 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 3:03 PM
Points: 31,276, Visits: 15,728
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 4, 2013 2:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 255, Visits: 1,009
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 5, 2013 8:16 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 3:03 PM
Points: 31,276, Visits: 15,728
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 5, 2013 8:27 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 737, Visits: 5,447
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 5, 2013 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:23 PM
Points: 7,135, Visits: 12,743
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
Post #1470799
Posted Friday, July 5, 2013 9:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 737, Visits: 5,447
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 5, 2013 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:23 PM
Points: 7,135, Visits: 12,743
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
Post #1470802
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse