May 5, 2008 at 5:26 am
I need to know the size used by the data files on my machine,
when I do a select * from sysperfinfo I can find the "total datafile size" but not the used size (like the log files) does anyone knows how to get the used datafile size (ONLY the data file size not the data + log file size !)
Kind regards,
Steve
May 5, 2008 at 5:41 am
Hi Steve,
You can try "select * from sys.database_files"
May 5, 2008 at 5:49 am
Nice I see there a column named "max_size", but at the moment i've set my data file to 100MB restricted growth, and the value in de column max_size is 12800, when I divide by 1024 I couldn't see this value is in MB, or in KB ...
does anyone knows this?
May 5, 2008 at 6:30 am
From BOL: "Maximum file size, in 8-KB pages"
100 MB * 1024 (KB) / 8
Hope that makes sense
May 5, 2008 at 6:40 am
Thank you verry mutch :)\0/
A last question: when I use more than 1 data file, will sql server automatically use the next file if one is full ? or does an DBA need to take action?
May 5, 2008 at 6:50 am
I'm not 100% sure, but I would guess that you need to (if you're the DBA) take action. I don't see SQL doing it automatically.
May 5, 2008 at 8:32 am
When you add a datafile to an existing filegroup, the SQL server will balance the storage across the files automatically. It won't move old data to the new file, but as new data comes in it will be stored in the new file, until a balance of storage is achieved. Then it will balance across the # of files evenly.
Your friendly High-Tech Janitor... 🙂
May 5, 2008 at 8:38 am
This script analyzes the size of all database datafiles on a server in a number of different ways for files size, uses, unused, etc.
Get Server Database File Information
May 5, 2008 at 9:00 am
Gordon (5/5/2008)
When you add a datafile to an existing filegroup, the SQL server will balance the storage across the files automatically. It won't move old data to the new file, but as new data comes in it will be stored in the new file, until a balance of storage is achieved. Then it will balance across the # of files evenly.
Thanks Gordon,
Just to confirm, SQL won't write data to a different filegroup if the existing one's file/s is full?
May 5, 2008 at 9:22 am
Not to different filegroups no.
Your friendly High-Tech Janitor... 🙂
May 6, 2008 at 6:54 am
Oke, i've used the sys.database_files table to get information about the data files of the database that is currently in use (with the USE statement you can change it).
So i've made a stored procedure like this:
ALTER PROCEDURE [xx].[name] @dbname varchar(255)
AS
BEGIN
declare @ber float
declare @dbexists int
declare @used float
declare @max_size float
declare @parm varchar(255)
exec @dbexists = xx.name @dbname
if (@dbexists=1)
BEGIN
exec ('use '+dbname)
exec(@parm)
With a parameter you give the database name and then the script will change the current database with the exec("use'+@dbname) statement, but when I execute the script nothings happens , there's no change to another database and the script is running on the database that is currently in use..
anyone?
May 6, 2008 at 2:17 pm
The EXEC('string') commands run in their own, individual, separate batches.
So these two commands:
exec ('use '+dbname)
exec(@parm)
Have no functional impact on each other. (also, I don't think that an expression can be used with EXEC(..), only a literal or a variable.)
You will need to merge them together like so:
Declare @sql Varchar(max)
Select @sql = 'use '+dbname + ';
' + @parm
EXEC(@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply