January 21, 2014 at 12:29 am
Carolyn S. White (1/20/2014)
Please excuse my ignorance.I need to change this how?
I get the error
Incorrect syntax near 'o'.
I tried changing SCHEMA_NAME to the name of the schema for the particular database. That didn't help.
It's the font that is used, or the type of single quote. Notice that in the original, which you copied and pasted, the single quotes are slanted, not perpendicular. Change it to the single quote you normally put around a string and it will work. There are two places, if I remember, where you have to change it. Then it will run.
Dana
January 21, 2014 at 9:49 am
I did that and got the same error.
I retyped everything and still got the same error.
This is what I typed in:
SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
reserved_page_count * 8 as space_used_kb,
row_count
FROM
sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
ORDER BY
SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)
====
Is there something I should check in my sqlserver setup?
Thank you.
January 21, 2014 at 10:07 am
First line of this script should be
SELECT
Add SELECT as your first line, anything else seems OK.
January 21, 2014 at 10:42 am
Yes, that fixed the problem. Sorry to waste everyone's time on such a stupid mistake in the copy/paste.
But.... for each table there seem to be double entries with different values for space_used_kb while the row count is the same in both entries. Does that mean the space is fragmented? Or is that just a feature of the RAID 5?
I've done a cut and paste of a few.
name space_used_kb row_count
gis,WQSTATIONS 3728 8331
gis,WQSTATIONS 208 8331
gis,WSD10PY3_DISSOLVE 136 410
gis,WSD10PY3_DISSOLVE 16 410
gis,WSD12PY3 584 1874
gis,WSD12PY3 56 1874
gis,ZCTABOUNDARIES 264 1384
gis,ZCTABOUNDARIES 48 1384
gis,ZCTABOUNDARIES_2000 264 1788
gis,ZCTABOUNDARIES_2000 56 1788
gis,ZCTABOUNDARIES_2000_T1 328 1788
gis,ZCTABOUNDARIES_2000_T1 56 1788
gis,ZCTABOUNDARIES_2000_T2 328 1788
gis,ZCTABOUNDARIES_2000_T2 56 1788
gis,ZCTABOUNDARIES_2000_T3 328 1788
gis,ZCTABOUNDARIES_2000_T3 56 1788
January 21, 2014 at 10:59 am
The result returns tables and all indexes, you see multiple rows of same table because these tables have indexes created on them. They are not related to fragmentation or operation system.
You can take a look at another query that aggregates table and its indexes here : http://rrlmblog.wordpress.com/2014/01/18/table-space-and-row-count-version-2/[/url]
You will need to replace the quotation marks in that script.
January 21, 2014 at 11:32 am
Carolyn S. White (1/21/2014)
I did that and got the same error.I retyped everything and still got the same error.
This is what I typed in:
SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
reserved_page_count * 8 as space_used_kb,
row_count
FROM
sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
ORDER BY
SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)
====
Is there something I should check in my sqlserver setup?
Thank you.
You have a comma in the quotes, it should be a period.
Dana
February 14, 2014 at 6:50 pm
Here is an alternate script that provides more complete information.
http://jasonbrimhall.info/2011/11/21/table-space-cs-part-deux/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply