Constructing a Query

Hello all,

I am fairly new to using SQL in a production environment. I've gotten the management studio pretty much figured out and now I am finding the need for some more advanced knowledge. I would like to adjust the database autogrowth settings for each of my databases; however I have no prior statistics to examine to tell me how much the past years worth of being in production has caused the databases to grow.

I have found that I can look at each database in turn and select a basic report of disk usage that happens to include records of the last few autogrowth periods and particulars about each growth. What I would like to do is create a report (The same disk usage report) that includes all of my databases (I have about 30 active databases for MS Dynamics Great Plains.) I figured that I can create a maintenance plan consisting of a transact-SQL function that will run the same query, or a similar query that will report to me: Autogrowth statistics for each database in turn since it's inception (less than a year ago).

Perhaps there is an easier way to achieve the same results. If there is a way to create / run the same disk usage report for multiple databases, and have the results returned in a consolidated form, I have not yet figured out how.

I hope that someone out there has a solution for this; what i'm sure is not a new issue but mearly a newbie issue.

Thank you all in advance for any advice you may have.

Normally, you don't want to use 'Autogrow' on production servers. Autogrow causes excessive file fragmentation, and often occurs at the most inopportune moments (Autogrow is a performance hit.)

You would be best served to determine how much new space will be required over a period of time (week, month, year, etc.) by examining the historic records you have. Then exand the database files sufficiently to allow for all activity for the next period of time.

|||Ok, I can accept that. I've got a maximum of about 8 users working on a very beefy sql box. I have no concern about overall preformance just yet. However I WILL take your advice and remove the autogrowth. I still, however need to collect some pervious growth statistics. My question still remains, is there a way to script a report that will just pull the autogrowth statistics for all of my databases from the time of their creation?|||No, as far as I am aware, that historical information is not available.|||

Ok, Then lets take one step further back.

Since there is already a builtin 'disk usage' report that can be run on a database. Can I use a Transact Sql query to pull that same report (which will contain autogrowth history) for all (or a select number) of databases and filter through it at my leasure?

If so, I'll probably need some help with scripting of said query.

Thank you for your time and advice,

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
> > Hopefully somebody here can help me, I am fairly new to SQL Server. I
have
> > SQL Server 2000 running on a Windows 2000 Server. I have a table called
> > StudentInfo with a field called Date. I want the Date field to only
accept
> > the current date. This field gets updated by teachers every day when
they
> do
> > their attendance. In Access I used a validation rule of date(). In SQL I
> > created a contraint on the StudentInfo table and in the constraint
> > expression I put Date = date(). I have unchecked "validate existing
data"
> > but left the other 2 boxes checked. However, I get the following error -
> > Error validating check constraint. I think I probably have the wrong
> syntax
> > in my contraint expression, can anybody help?
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
have
accept
they
> do
data"
> syntax
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

consolidation of datafiles

i'm fairly new at this. can someone help.

i inherited a server where people have been throwing datafiles/indexfiles/logfiles all over the place due to space limitations. i've been approved for more space on the server and am trying to reorganize the filesystem. can someone tell me or point me in the right direction...

how to consolidate multiple log files (i understand having more then 1 log file is completely useless)

how to move datafiles to different drives

how to consolidate multiple datafiles... etc.

any help would be greatly appreciated.

Look in Books Online about using [ DBCC SHRINKFILE ], especially with the EMPTYFILE option.

Moving datafiles is as simple as DETACH thedatabase, copy the files to new location, and the ATTACH the database. You can use the GUI in EM/SSMS, or sp_detach_db and sp_attach_db. The database will be 'off line' as long as it takes to copy the files and re-attach.