Solved

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

Posted on 2016-08-12
3
150 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 13

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL syntax for max(date) 3 39
What does "Between" mean? 6 48
Need split for SQL data 7 49
Access VBA for Search Engine 7 14
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 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