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.
namespace_used_kbrow_count
gis,WQSTATIONS37288331
gis,WQSTATIONS2088331
gis,WSD10PY3_DISSOLVE136410
gis,WSD10PY3_DISSOLVE16410
gis,WSD12PY35841874
gis,WSD12PY3561874
gis,ZCTABOUNDARIES2641384
gis,ZCTABOUNDARIES481384
gis,ZCTABOUNDARIES_20002641788
gis,ZCTABOUNDARIES_2000561788
gis,ZCTABOUNDARIES_2000_T13281788
gis,ZCTABOUNDARIES_2000_T1561788
gis,ZCTABOUNDARIES_2000_T23281788
gis,ZCTABOUNDARIES_2000_T2561788
gis,ZCTABOUNDARIES_2000_T33281788
gis,ZCTABOUNDARIES_2000_T3561788
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