July 2, 2013 at 5:20 pm
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
July 2, 2013 at 6:51 pm
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.
July 3, 2013 at 9:11 am
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
July 3, 2013 at 9:15 am
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.
July 4, 2013 at 2:58 am
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-2 - Thanks for the extra info on this. By the way - terrible shirt at the sql in the city in London !!!! 😉
Fantastic day though !!
July 5, 2013 at 8:27 am
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.
July 5, 2013 at 9:40 am
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
July 5, 2013 at 9:45 am
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.
😉
July 5, 2013 at 9:48 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply