Automating Capacity planning of the disks

I will like to write a short code to automate the
procedure of checking the total space available on the
disks on the server toavoid running out of space and to
avoid manual intervention. Any help please.See if this helps: http://www.sqldbatips.com/showcode.asp?ID=4
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c24301c47a34$4a90d3f0$a301280a@.phx.gbl...
I will like to write a short code to automate the
procedure of checking the total space available on the
disks on the server toavoid running out of space and to
avoid manual intervention. Any help please.

Automatically grow file did not function.

My server SQL7.0 SP4 found the problem Automatically grow file did not function.
I try to manaully input to Space Allocated (MB) but I receive error as below
fcb::ZeroFile(): GetOverLappedResult() failed with error 121.This error was associated with disk problems in the past. I'd try to verify
the interity of the disk.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Jittima D." <anonymous@.discussions.microsoft.com> wrote in message
news:F310CAB1-E3B8-48A0-B90A-28D5C3273DEE@.microsoft.com...
> My server SQL7.0 SP4 found the problem Automatically grow file did not
function.
> I try to manaully input to Space Allocated (MB) but I receive error as
below
> fcb::ZeroFile(): GetOverLappedResult() failed with error 121.
>

automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
George
There is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George
|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>

automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic
SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gb
l...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>

Automatically grow database

If the autogrow is set on a database primary file group
when does the server actually grow the file? Does it wait
for an out of space condition or is it automated?It is before an out of space condtion. But you can set up a job to automate
it with the ALTER DATABASE statement.
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:5ed001c3df71$7aa9c230$7d02280a@.phx.gbl...

quote:

---

> If the autogrow is set on a database primary file group
> when does the server actually grow the file? Does it wait
> for an out of space condition or is it automated?

---

|||The reason I ask is that we have the primary file group to
autogrow at 100mb. We have a maintenance plan that runs
every Sunday. For the past 2 Sunday's the job has failed
due to out of space. There is plenty of space on the
drive for the file to grow. Not sure why the job is
failing on space issue. That is why I ask if it is on
error does it grow.|||Having autogrow is better than nothing. But better yet, don't leave it
solely for SQL Server. The better way is to size your db, forecast its
growth, and allocate space accordingly. Leave the autogrow on but keep
checking back whether there is need of growing again, and if needed, do it
manually at a not-so-busy time. Autogrow can take time so long that your
application may error out while waiting for the growth (though it's not
likely in your case of autogrow size).
Not sure what your problem is. You have a plan to grow the db file every
sunday? That doesn't sound right. What for job was failing? What's the
role of the maintenance plan in your problem?
<anonymous@.discussions.microsoft.com> wrote in message
news:126301c3df8b$67613d60$a001280a@.phx.gbl...

quote:

---

> The reason I ask is that we have the primary file group to
> autogrow at 100mb. We have a maintenance plan that runs
> every Sunday. For the past 2 Sunday's the job has failed
> due to out of space. There is plenty of space on the
> drive for the file to grow. Not sure why the job is
> failing on space issue. That is why I ask if it is on
> error does it grow.

---

Automatically grow database

If the autogrow is set on a database primary file group
when does the server actually grow the file? Does it wait
for an out of space condition or is it automated?Yes, on reaching its current size it will grow by either a
percentage of the current size or as a fixed number of MB.
It will take the space on the Hard Disk up, so make sure
you have plenty of disk space.
J
>--Original Message--
>If the autogrow is set on a database primary file group
>when does the server actually grow the file? Does it
wait
>for an out of space condition or is it automated?
>.
>|||It is before an out of space condtion. But you can set up a job to automate
it with the ALTER DATABASE statement.
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:5ed001c3df71$7aa9c230$7d02280a@.phx.gbl...
> If the autogrow is set on a database primary file group
> when does the server actually grow the file? Does it wait
> for an out of space condition or is it automated?|||The reason I ask is that we have the primary file group to
autogrow at 100mb. We have a maintenance plan that runs
every Sunday. For the past 2 Sunday's the job has failed
due to out of space. There is plenty of space on the
drive for the file to grow. Not sure why the job is
failing on space issue. That is why I ask if it is on
error does it grow.|||Having autogrow is better than nothing. But better yet, don't leave it
solely for SQL Server. The better way is to size your db, forecast its
growth, and allocate space accordingly. Leave the autogrow on but keep
checking back whether there is need of growing again, and if needed, do it
manually at a not-so-busy time. Autogrow can take time so long that your
application may error out while waiting for the growth (though it's not
likely in your case of autogrow size).
Not sure what your problem is. You have a plan to grow the db file every
sunday? That doesn't sound right. What for job was failing? What's the
role of the maintenance plan in your problem?
<anonymous@.discussions.microsoft.com> wrote in message
news:126301c3df8b$67613d60$a001280a@.phx.gbl...
> The reason I ask is that we have the primary file group to
> autogrow at 100mb. We have a maintenance plan that runs
> every Sunday. For the past 2 Sunday's the job has failed
> due to out of space. There is plenty of space on the
> drive for the file to grow. Not sure why the job is
> failing on space issue. That is why I ask if it is on
> error does it grow.

Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Automate Admin Activities

Hi,
I want to automate some administrative activities (Check Integrity, Shrink
DB Log Space after backup, rebuild indexes...).
I'm using Database Maintenance Plan, is this ok?
After I run these jobs, in the job history and in the log generated I just
see that
the activity succeded, but not what was done.
For example, I would like to know how big was the Transaction Log before the
Shrink and after.
Please help, thanks.Tarek,
For the most part Database Maintenance Plans are fine for most smaller to
medium sized databases. One limitation is the lack of support for
differential backups. MPs can be more difficult to troubleshoot at times as
well...so many DBAs will create their own custom jobs to mimick and extend
the capabilities of MPs. You can view the history of the job or the history
of the MP to get the "run" details. However, this information will not
provide you with detailed specifics as before/after. You might be able to
add additional job steps to the MP job to track additional before/after
data.
HTH
Jerry
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:41F68B47-0B1D-4F75-B513-C4325A9FE591@.microsoft.com...
> Hi,
> I want to automate some administrative activities (Check Integrity, Shrink
> DB Log Space after backup, rebuild indexes...).
> I'm using Database Maintenance Plan, is this ok?
> After I run these jobs, in the job history and in the log generated I just
> see that
> the activity succeded, but not what was done.
> For example, I would like to know how big was the Transaction Log before
> the
> Shrink and after.
> Please help, thanks.|||Thanks Jerry for your response.
Where can I find some templates on how to achieve these jobs:
- Check DB Integrity
- Rebuild Indexes
- Gather Statistics
For example on the help of the DBCC ShowConting I found a script to defrag
all indexes of the database, can this be ok?
I'm not very confident with sqlserver. I'm a dba but not on sql so I'm
trying to learn how to do dba activities here.
Thanks
"Jerry Spivey" wrote:

> Tarek,
> For the most part Database Maintenance Plans are fine for most smaller to
> medium sized databases. One limitation is the lack of support for
> differential backups. MPs can be more difficult to troubleshoot at times
as
> well...so many DBAs will create their own custom jobs to mimick and extend
> the capabilities of MPs. You can view the history of the job or the histo
ry
> of the MP to get the "run" details. However, this information will not
> provide you with detailed specifics as before/after. You might be able to
> add additional job steps to the MP job to track additional before/after
> data.
> HTH
> Jerry
> "Tarek" <Tarek@.discussions.microsoft.com> wrote in message
> news:41F68B47-0B1D-4F75-B513-C4325A9FE591@.microsoft.com...
>
>|||Tarek,
There are a variety of scripts out there to work with...just have to search
for the various ones. This site lists several valuable websites you might
start with. Google is a good place too. Be sure to fully test out any
downloaded scripts in a test environment first prior to introducing them
into a production environment.
SQL Server Communities
http://www.microsoft.com/sql/commun...ommunities.mspx
HTH
Jerry
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:5A84FD1D-B679-40C6-A2D6-D0D78F0DE9E1@.microsoft.com...
> Thanks Jerry for your response.
> Where can I find some templates on how to achieve these jobs:
> - Check DB Integrity
> - Rebuild Indexes
> - Gather Statistics
> For example on the help of the DBCC ShowConting I found a script to defrag
> all indexes of the database, can this be ok?
> I'm not very confident with sqlserver. I'm a dba but not on sql so I'm
> trying to learn how to do dba activities here.
> Thanks
>
>
> "Jerry Spivey" wrote:
>

autogrow history

Hi,
Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.
Thanks,
SusanThis kind of error should be logged on SQL Server Logs.

Originally posted by dbadba
Hi,

Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.

Thanks,
Susan|||Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Laura,

As far as I know, there is no way to create an alert based on autogrow event, unless you fix a log size limit and then add an alert using "error 9002, severity 19 - The log file for database xxxx is full". I think there is even a template in SQL 2000 Alerts for that.
Another approach would be to define a trace based on transaction log autogrow event. You can use SQL Profiler for tracing; or it can generates the SQL script for you, and then you can include it in a stored procedure that can be scheduled to start at a specific time.

Hope it helps !

Originally posted by dbadba
Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Thanks! I will check into both of the options you suggested.|||if you want to prevent this kind of error from happening you can also create an alert that is triggered when a log is more than a certain percentage full.|||Thank you very much for the suggestion! I appreciate it.|||UNtested but this could be run regularly to email you when the log is over 50% full

create procedure mylog @.dbname varchar(50)
as
declare @.logspace int
, @.wo_num varchar(30) , @.deleted_user varchar(255),@.emails varchar(255) , @.messages varchar(100),
@.subjecttext varchar(100)
truncate table tempdb..logspace
insert tempdb..logspace (dbname,logsize,percentused,status)
execute('dbcc perflog')
set @.logspace = (select percentused from tempdb..logspace where dbname = @.dbname)
print @.logspace
if @.logspace > 50
set @.messages = 'Log Space is over 50%'
set @.subjecttext = @.dbname + 'log is over 50% full'
set @.emails = 'joesmoe@.kokomo.com'
exec master..xp_sendmail @.recipients = @.emails , @.message = @.messages,@.subject = @.subjecttext
else
return

HTH

Auto grow

I have two files set up for one filegroup and the primary file was set to NO
AUTO GROW since the drive it was pointing to does not have enough space
available. The secondary file was set to AUTO GROW and the drive space has
enough available space. Provided that the secondary file has plenty of room
for new insert will there be any case that the new INSERT will fail? I am
trying to troubleshoot an issue on which the INSERT was failing but when I
was reset the primary file to AUTO GROW the problem disappeared.Yes SQL server will write proportionally to the files according to their
allocated sizes. You will have to move the file that can no longer grow to
a drive where it has space to grow. Or you may put the content of the file
that can no longer grow into the other file(s).
hth
Quentin
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
>I have two files set up for one filegroup and the primary file was set to
>NO
> AUTO GROW since the drive it was pointing to does not have enough space
> available. The secondary file was set to AUTO GROW and the drive space
> has
> enough available space. Provided that the secondary file has plenty of
> room
> for new insert will there be any case that the new INSERT will fail? I am
> trying to troubleshoot an issue on which the INSERT was failing but when I
> was reset the primary file to AUTO GROW the problem disappeared.|||If there are two files in a database, and one file is full without the AUTO
GROW setting, then SQL Server should only try to allocate from the other
file. So most likely the insert will not fail as long as the other file can
auto grow or has free space.
From a performance point of view, this is not the ideal situation for your
database. We recommend that the database files are configured so that each
file has some free space in it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:#um$101dFHA.2960@.TK2MSFTNGP10.phx.gbl...
> Yes SQL server will write proportionally to the files according to their
> allocated sizes. You will have to move the file that can no longer grow
to
> a drive where it has space to grow. Or you may put the content of the
file
> that can no longer grow into the other file(s).
> hth
> Quentin
> "J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
> news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
> >I have two files set up for one filegroup and the primary file was set to
> >NO
> > AUTO GROW since the drive it was pointing to does not have enough space
> > available. The secondary file was set to AUTO GROW and the drive space
> > has
> > enough available space. Provided that the secondary file has plenty of
> > room
> > for new insert will there be any case that the new INSERT will fail? I
am
> > trying to troubleshoot an issue on which the INSERT was failing but when
I
> > was reset the primary file to AUTO GROW the problem disappeared.
>