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.


more ▼

asked Oct 18, 2011 at 12:52 PM in Default

avatar image

Barbara Plume
85 7 7 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Oct 18, 2011 at 01:17 PM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

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, 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:

     name, len(name) nameLength 
     from master.dbo.sysdatabases
     order by 2 desc;

Oct 18, 2011 at 03:22 PM Oleg

Thanks, Oleg.

I'm back on track.

Oct 25, 2011 at 09:04 AM Barbara Plume
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 18, 2011 at 12:52 PM

Seen: 2158 times

Last Updated: Oct 18, 2011 at 12:52 PM

Copyright 2018 Redgate Software. Privacy Policy