x

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
more ▼

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

Barbara Plume gravatar image

Barbara Plume
85 7 7 7

(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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1833
x18

asked: Oct 18, 2011 at 12:52 PM

Seen: 1444 times

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