Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting error when trying to get standard reports in SQL Server Management Studio

Posted on 2016-08-12
3
Medium Priority
?
265 Views
Last Modified: 2016-08-16
I am trying to get the size for a particular Database in one of the the SQL Servers using SSMS. I goto the Database --> Reports --> Standard report and Disk usage... I am getting this error: " Index (zero based) must be greater than or equal to zero and less than the size of the argument list."

My questions  :
1. what does this mean? Should I be concerned with something?
2. Is there another way to get the size?

Any help will be highly appreciated.
0
Comment
Question by:Byas_Saha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41754044
seems like the database computability is not set properly ; check version of SSMS and th compatibility of your database;  Assuming its not production, you could change the compatibility of the database to match SSMS
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41754075
You don't need to use the report to get the size of a database. Use this view:

CREATE VIEW [dbo].[vwGetDatabasesInfo]
as
/*
	select * from dbo.vwGetDatabasesInfo
--*/
SELECT 
     SERVERPROPERTY ( 'MachineName' ) as HostName,
     SERVERPROPERTY ( 'servername' ) as ServerName,
     SERVERPROPERTY ( 'instancename' ) as InstanceName,
     SERVERPROPERTY ( 'edition' ) as ServerEdition,
     SERVERPROPERTY ( 'ProductVersion' ) as ServerVersion,
     SERVERPROPERTY ( 'collation' ) as ServerCollation,
	cast(db.Name as varchar(50)) AS DatabaseName,
	db.create_date as DatabaseCreateDate,
	db.collation_name as DatabaseCollation,
	db.[compatibility_level] as DatabaseCompatibility,
	cast(DATABASEPROPERTYEX(db.Name, 'Status') as varchar(10)) AS DBStatus,
	cast(DATABASEPROPERTYEX(db.Name, 'Recovery') as varchar(10)) AS Recovery,
	cast(DATABASEPROPERTYEX(db.Name, 'Updateability') as varchar(15)) AS Updateability,
	cast(DATABASEPROPERTYEX(db.Name, 'UserAccess') as varchar(15)) AS UserAccess,
	cast(round(RowSizeMB,2) as decimal(10,2)) as RowSizeMB,
	cast(round(LogSizeMB,2) as decimal(10,2)) as LogSizeMB,
	cast(round(StreamSizeMB,2) as decimal(10,2)) as StreamSizeMB,
	cast(round(TextIndexSizeMB,2) as decimal(10,2)) as TextIndexSizeMB,
	cast(round(TotalSizeMB,2) as decimal(10,2)) as TotalSizeMB,
	fl.[file_id],
	fl.name as file_logical_name,
	fl.physical_name as physical_file_name,
	fl.type_desc as file_type,
	cast(round(fl.size*8.00/1024,2) as decimal(10,2)) as physical_file_sizeMB,
	fl.state as file_state,
	cast(fl.max_size as bigint)*8/1024 as file_max_sizeMB,
	fl.growth*8/1024 as file_growth,
	left(case fl.is_percent_growth when 1 then '%' else 'MB' end,2) as file_growth_type
	,num_of_reads
	,num_of_bytes_read
	,io_stall_read_ms
	,num_of_writes
	,num_of_bytes_written
	,io_stall_write_ms
	,io_stall
from 
	master.sys.databases db 
	inner join
	( -- get the db sizes
		SELECT
			db.database_id,
			(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
			(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
			(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
			(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB,

			(CAST(mfrows.RowSize AS FLOAT)*8)/1024+
			(CAST(mflog.LogSize AS FLOAT)*8)/1024+
			isnull((CAST(mfstream.StreamSize AS FLOAT)*8)/1024,0)+
			isnull((CAST(mftext.TextIndexSize AS FLOAT)*8)/1024,0) as TotalSizeMB
		    
		FROM master.sys.databases db
			LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM master.sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
			LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM master.sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
			LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM master.sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
			LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM master.sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
	) dbsize
		on dbsize.database_id=db.database_id
	inner join master.sys.master_files fl
		on fl.database_id=db.database_id
	inner join sys.dm_io_virtual_file_stats(null, null) AS divfs
		on divfs.database_id=db.database_id
		and divfs.[file_id]=fl.[file_id]

Open in new window

0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41755006
I suspect your SSMS version does not match the version of the SQL instance (for example, if the SQL instance is on SQL 2012, you may be using a SQL 2008 instance of SSMS). Please try to see if you can use the appropriate instance of SSMS.

This is an issue with the tools being used to utilize to the SQL Server reporting services. I do not believe changing the database compatibility level will help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question