May 13, 2005 at 10:03 am
how can you get a list of databases set to unrestricted file growth with out opening each one? Thanks!
May 13, 2005 at 12:20 pm
This is set on a file-by-file basis, not for an entire database. The setting is stored in sysfiles, in the maxsize column. -1 indicates unrestricted growth.
May 13, 2005 at 12:46 pm
Yes but you can also set this in enterprise manager. I am dealing with a large amont of databases and some of the databases are set to unrestricted file growth. Going through each database to remove this will take a rather long time. Thanks!
May 13, 2005 at 1:43 pm
This should work I think, but it is written from memory with no server to test on so I cannot guarantee it:
EXEC sp_MSforeachdb @command1 = 'SELECT ''?'' AS DatabaseName, name AS LogicalFileName, filename AS PhysicalFilePath FROM ?.dbo.sysfiles WHERE maxsize = -1'
May 13, 2005 at 4:27 pm
Got this error, which ther is no 4 to start with. I also changed it up a little with no success. What do you think may be the cause?
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
Thanks!
May 16, 2005 at 1:00 am
Hm, sorry, no idea. I tested the statement and it worked perfectly for me. Which version of SQL Server are you using?
Did you run just that statement by itself in a new session in Query Analyzer? It looks to me as though there were some other statements in the batch and the parsing did not go through.
If you do not get it to work then here is the relevant part from sp_MSforeachdb. You can execute this by itself and see if you can pinpoint the error better.
exec(N'declare hCForEach cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & 0x80000000 = 0)' +
N' and ((DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1)) or ' +
N' ( DATABASEPROPERTY(d.name, ''issingleuser'') = 1 and not exists ' +
N' (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid @@spid)))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker 'SELECT ''?'' AS DatabaseName, name AS LogicalFileName, filename AS PhysicalFilePath FROM ?.dbo.sysfiles WHERE maxsize = -1'
May 19, 2005 at 3:12 pm
I tryed that and recieved errors also. I am working with SQL 2000 if that helps. Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply