Solved

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

Posted on 2016-08-12
3
192 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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