asked
Long SharePoint database names causing 'USE ?; EXEC sp_spaceused' to abort
EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused' In using one variation of the above code, I ran into a problem reporting the sizes of Microsoft SharePoint databases because the name of at least one of the databases is being truncated in the sp_MSforeachdb code somewhere, which causes the procedure to abort. Has anyone mentioned a solution to this before? I looked but didn't find one. My plan was to use the above to store database space used information in a table for ultimate reporting purposes. Thank you. Barbara
try this: EXEC sp_MSForEachDB 'USE [?]; EXEC sp_spaceused' Chances are your database names have "dodgy" characters in their names - wrapping the name in square brackets will protect against it. By "dodgy" I mean hyphens (or minus signs), spaces - that sort of thing.
This part is actually taken care of by the procedure implementation. When it instantiates the global cursor, it uses select **name** from master.dbo.sysdatabases which includes the where clause checking for **databaseproperty** and **has\_dbaccess**. All 3 are sysname meaning **nvarchar(128)**. In other words, there should be no problems with the size of the database name. Per [BOL][1], it is not possible to create a database with more than 128 characters in its name:
database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. If a logical log file name is not specified, SQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.What is the value in the second column of the first row if you run this: select name, len(name) nameLength from master.dbo.sysdatabases order by 2 desc; [1]: http://msdn.microsoft.com/en-us/library/ms176061.aspx
