Urgent help needed, Max Growth by DB

Question

Post reply

Urgent help needed, Max Growth by DB

anzz

SSCertifiable

Points: 5503
More actions
July 10, 2012 at 12:46 pm

#263671

Urgent help needed, Thanks in advance.
Can I get T- SQL Script which displays for all the databases on the server.
DataName,
Physical_location,
DataSize,
DBGgrowth,
DB Max,
LogSize,
LogGrowth,
Log Max.

Viewing 15 posts - 1 through 15 (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

Here's a starter for you

Select * from sys.master_files

From there, you can retrieve the information you need and customize the script to your needs.

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 2

Thanks SQLRNNR

But I was not able to get the right info Size, Can I have it in MB display.

Logical Name,

Physical_location,

DataSize,

DBGrowth,

DB Max,

LogSize,

LogGrowth,

Log Max.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

responses are being fragmented

Post further responses here

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You should take a look at what the columns mean. http://msdn.microsoft.com/en-us/library/ms186782.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

Everything except for the DB name can be found in sys.database_files. You can get db name with DB_NAME(). The procedure below will get you all this information for every DB on the SQL Instance you run it on.

Happy Databasing!

IF object_id('tempdb..#x') IS NOT NULL

DROP TABLE #x;

CREATE TABLE #x

(

[db]varchar(100),

[FileType]varchar(30),

[Physical_location]varchar(200),

[Size(mb)]int,

[MaxSize(mb)]bigint,

growthint,

is_percent_growthbit

);

EXEC sp_msforeachdb '

USE [?];

INSERT INTO #x

SELECTDB_NAME() [db],

type_desc [FileType],

physical_name [Physical_location],

size [Size(mb)],

max_size [MaxSize(mb)],

growth,

is_percent_growth

FROM sys.database_files

'

SELECT[db],

CASE (FileType)

WHEN 'ROWS' THEN 'Data file'

WHEN 'LOG' THEN 'Log File'

ELSE FileType

END [FileType],

[Physical_location],

([Size(mb)] * 8)/1024 [size(mb)],

([MaxSize(mb)] * 8)/1024 [MaxSize(mb)],

CASE

WHEN is_percent_growth = 0

THEN CAST(growth * 8/1024 AS varchar(10)) + 'MB'

ELSE

CAST(growth as varchar(10)) + '%'

END AS [File Growth]

FROM #x

ORDER BY [db], [FileType]

"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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

XMLSQLNinja (7/10/2012)
Everything except for the DB name can be found in sys.database_files. You can get db name with DB_NAME(). The procedure below will get you all this information for every DB on the SQL Instance you run it on.
Happy Databasing!
IF object_id('tempdb..#x') IS NOT NULL
DROP TABLE #x;
CREATE TABLE #x
(
[db]varchar(100),
[FileType]varchar(30),
[Physical_location]varchar(200),
[Size(mb)]int,
[MaxSize(mb)]bigint,
growthint,
is_percent_growthbit
);
EXEC sp_msforeachdb '
USE [?];
INSERT INTO #x
SELECTDB_NAME() [db],
type_desc [FileType],
physical_name [Physical_location],
size [Size(mb)],
max_size [MaxSize(mb)],
growth,
is_percent_growth
FROM sys.database_files
'
SELECT[db],
CASE (FileType)
WHEN 'ROWS' THEN 'Data file'
WHEN 'LOG' THEN 'Log File'
ELSE FileType
END [FileType],
[Physical_location],
([Size(mb)] * 8)/1024 [size(mb)],
([MaxSize(mb)] * 8)/1024 [MaxSize(mb)],
CASE
WHEN is_percent_growth = 0
THEN CAST(growth * 8/1024 AS varchar(10)) + 'MB'
ELSE
CAST(growth as varchar(10)) + '%'
END AS [File Growth]
FROM #x
ORDER BY [db], [FileType]

You can simplify that by just using sys.master_files.

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 7

To get the number in MB:

value * 8 / 1024

Example:

Selectname,

size*8/1024

from sys.master_files

"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 8

Thnaks XMLSQLNinja

Can we add 'Available Free Space of .mdf, .ndf, .ldf' to existing script

Your help is greatly appreciated!

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

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 128.

--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)

anzz SSCertifiable Points: 5503 More actions · Answer 10

I need Available Free Space on of .mdf, .ndf, .ldf on whole instance addinf to existing script.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

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

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 12

Danzz (7/10/2012)
I need Available Free Space on of .mdf, .ndf, .ldf on whole instance addinf to existing script.

You have been given enough info to find that info now. This is where you should make an effort at figuring that part out. You do that, then you will be on your way to becoming a better dba

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 13

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.

--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)

anzz SSCertifiable Points: 5503 More actions · Answer 14

I dont get any info from ' Select * from sys.master_files '.

Help please!