Urgent help needed, Max Growth by DB

Question

Urgent help needed, Max Growth by DB

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Danzz (7/10/2012)
I dont get any info from ' Select * from sys.master_files '.
Help please!

Do you get an error?

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Jeff Moden (7/10/2012)
SQLRNNR (7/10/2012)
Jeff Moden (7/10/2012)
XMLSQLNinja (7/10/2012)
To get the number in MB:
value * 8 / 1024
Example:
Selectname,
size*8/1024
from sys.master_files
Just divide by 124.
Should be 128
Wow... talk about phat phingering. Thanks for the catch and the correction.

I should have included a smiley on that one:hehe:. Sorry Jeff.;-)

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

anzz SSCertifiable Points: 5503 More actions · Answer 3

No SQLRNNR

I don't get any information for Available Free Space on of .mdf, .ndf, .ldf

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Well, did you integrate the FILEPROPERTY function like Jack Corbett recommended on the other thread?

http://www.sqlservercentral.com/Forums/Topic1327652-391-1.aspx

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

You mean 128. Yes, that is correct. Thanks!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

anzz SSCertifiable Points: 5503 More actions · Answer 6

It gives only single database, I need for whole instance.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Post the query that you decided to settle on adapting for your needs. I am assuming that you followed recommendation and actually made changes to queries to adapt rather than just using a query without changing it.

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 8

SQLRNNR (7/10/2012)
I should have included a smiley on that one:hehe:. Sorry Jeff.;-)

No smiley face required on something like that. I'm glad you caught my mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

CREATE TABLE #FileProp (dbid INT,FILE_ID INT, SpaceUsed DECIMAL(14,2));

GO

EXECUTE sp_msforeachdb 'USE ?; Insert Into #FileProp (dbid,FILE_ID,SpaceUsed)

SELECT database_id,file_id,FILEPROPERTY(name,''SpaceUsed'') from sys.master_files where DB_NAME(database_id) = ''?'''

SELECT DB_NAME(database_id) AS DBName,physical_name,CONVERT(DECIMAL(14,2),size)/128 AS FileSize, growth

,CONVERT(DECIMAL(14,2),max_size)/128 AS MaxFileSize,FP.SpaceUsed,mf.type_desc

FROM sys.master_files mf

INNER JOIN #FileProp FP

ON FP.dbid = mf.database_id

AND FP.FILE_ID = mf.file_id;

DROP TABLE #FileProp;

GO

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

This script gets all the infomation you wanted for all databases on a server, and runs various queries to analyze it different ways. It works with SQL Server 7.0, 2000, 2005, 2008, and 2008 R2. I haven't tested it with SQL 2012.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

anzz SSCertifiable Points: 5503 More actions · Answer 11

Thanks SQLRNNR.

When I try to execute it gives an error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '3'.

I think it is not recognizing the databases starting with Integers(I mean numbers).

Any help on this please.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

CREATE TABLE #FileProp (dbid INT,FILE_ID INT, SpaceUsed DECIMAL(14,2));

GO

EXECUTE sp_msforeachdb 'USE [?]; Insert Into #FileProp (dbid,FILE_ID,SpaceUsed)

SELECT database_id,file_id,FILEPROPERTY(name,''SpaceUsed'') from sys.master_files where DB_NAME(database_id) = ''?'''

SELECT DB_NAME(database_id) AS DBName,physical_name,CONVERT(DECIMAL(14,2),size)/128 AS FileSize, growth

,CONVERT(DECIMAL(14,2),max_size)/128 AS MaxFileSize,FP.SpaceUsed,mf.type_desc

FROM sys.master_files mf

INNER JOIN #FileProp FP

ON FP.dbid = mf.database_id

AND FP.FILE_ID = mf.file_id;

DROP TABLE #FileProp;

GO

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

anzz SSCertifiable Points: 5503 More actions · Answer 13

Thanks SQLRNNR

In which line did you made changes?

anzz SSCertifiable Points: 5503 More actions · Answer 14

anzz

SSCertifiable

Points: 5503

July 12, 2012 at 1:14 pm

#1512270

Got it USE [?]

Thanks for your help.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

you are welcome

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