Low hard drive space on WIN 2K Server 
Author Message
 Low hard drive space on WIN 2K Server

Problem:
Using WIN 2K Server, running MS SQL Server 2K and only
have 68MB hard drive space left on server. A previous sa
failed to check the daily db maintenance plan backups, let
the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
Log file grow excessively  which has now used most of our
hard drive space. We only have a 34GB drive. To add insult
to injury, the autoloader was not backing up correctly,
but it now works ok. Backed up the aforementioned
Transaction Log last night to tape + the entire db, but
that process did not clear the Transaction Log, it is
still at 26.1GB. Perhaps, the bu did not work correctly
because of low hard drive space? I'll check the event logs
shortly.
Question # 1: Why did the Transaction Log not clear after
the tape backup?
Question # 2: Is there a quick fix to regain sufficient
hard drive space without causing db or Transaction Log
problems?
Question # 3: Is there a manual process to clear the
Transaction Log, if so how does it work?
Question # 4: Any quick recommendations, fixes to avoid a
system crash because of insufficient hard drive disk space
on the server? We are currently receiving event log
warnings to delete files (already completed what we could)
because of our hard drive is near capacity.
Please respond quickly as we are not far from a system
crash. Thanks for any help.

Mike



Sun, 02 Oct 2005 16:17:54 GMT
 Low hard drive space on WIN 2K Server

Answer# 1.  The log did likely clear.  It did not shrink.  Shrinking the log
is a manual task.  See BOL for details on exactly how to do so.
Answer# 2.  See Answer# 1
Answer# 3.  Again, clearing the unused portion of the log and shrinking the
log are different tasks.  I suggest reading the entire section on backup and
restore in BOL.  Backups are THE primary responsibility for a DBA.
Answer# 4.  Read quickly. :)

Seriously, there is a standard procedure that is well documented in BOL on
how to fix this.  I usually set an alert for 'near full log' that triggers
an alert.  That won't show autoexpand, but can give you some idea of how
often to schedule backups and when to watch the filesystem.

--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com


Quote:
> Problem:
> Using WIN 2K Server, running MS SQL Server 2K and only
> have 68MB hard drive space left on server. A previous sa
> failed to check the daily db maintenance plan backups, let
> the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
> Log file grow excessively  which has now used most of our
> hard drive space. We only have a 34GB drive. To add insult
> to injury, the autoloader was not backing up correctly,
> but it now works ok. Backed up the aforementioned
> Transaction Log last night to tape + the entire db, but
> that process did not clear the Transaction Log, it is
> still at 26.1GB. Perhaps, the bu did not work correctly
> because of low hard drive space? I'll check the event logs
> shortly.
> Question # 1: Why did the Transaction Log not clear after
> the tape backup?
> Question # 2: Is there a quick fix to regain sufficient
> hard drive space without causing db or Transaction Log
> problems?
> Question # 3: Is there a manual process to clear the
> Transaction Log, if so how does it work?
> Question # 4: Any quick recommendations, fixes to avoid a
> system crash because of insufficient hard drive disk space
> on the server? We are currently receiving event log
> warnings to delete files (already completed what we could)
> because of our hard drive is near capacity.
> Please respond quickly as we are not far from a system
> crash. Thanks for any help.

> Mike



Sun, 02 Oct 2005 17:27:55 GMT
 Low hard drive space on WIN 2K Server
Mike,

I received your e-mail, however hotmail has a limit of
messages I can send in a 24HR period, so it didn't send
the response.....

Question #1: The transaction log be cleared after the
backup, however the space allocated remains the same unles
you execute the DBCC SHRINKFILE command for the
transaction log file.

Question #2: Shrinking the file periodically will help you
to re-gain space

Question #3: I would suggest to use the Database
Maintenance Plans first, or use the xp_sqlmaint extended
stored procedure. You can create a job that executes the
transaction log file backup on the first step and the DBCC
SHRINKFILE on the second step to release the space the
file took to allocate all the transactions.

Question #4: I have a stored procedure that checks for
space usage on all my servers. I have a job that executes
that procedure periodically and alerts me if the drive
space is less than the amount set. I can send you the
script if you are interested.

Quote:
>-----Original Message-----
>Problem:
>Using WIN 2K Server, running MS SQL Server 2K and only
>have 68MB hard drive space left on server. A previous sa
>failed to check the daily db maintenance plan backups,
let
>the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
>Log file grow excessively  which has now used most of our
>hard drive space. We only have a 34GB drive. To add
insult
>to injury, the autoloader was not backing up correctly,
>but it now works ok. Backed up the aforementioned
>Transaction Log last night to tape + the entire db, but
>that process did not clear the Transaction Log, it is
>still at 26.1GB. Perhaps, the bu did not work correctly
>because of low hard drive space? I'll check the event
logs
>shortly.
>Question # 1: Why did the Transaction Log not clear after
>the tape backup?
>Question # 2: Is there a quick fix to regain sufficient
>hard drive space without causing db or Transaction Log
>problems?
>Question # 3: Is there a manual process to clear the
>Transaction Log, if so how does it work?
>Question # 4: Any quick recommendations, fixes to avoid a
>system crash because of insufficient hard drive disk
space
>on the server? We are currently receiving event log
>warnings to delete files (already completed what we
could)
>because of our hard drive is near capacity.
>Please respond quickly as we are not far from a system
>crash. Thanks for any help.

>Mike
>.



Sun, 02 Oct 2005 17:39:37 GMT
 Low hard drive space on WIN 2K Server
Geoff,

Thanks very much for your help.

The system is set as follows:
DB maintenance Plan 1

Optimization Tab:
Reorganize data and index pages (box checked)
Change Free Space per page percentage to: 10%
Remove unused space from database files (box checked)
Shrink databse when it grows beyond: 50MB
Amount of free space to remain after shrink: 10% of the
data space
Schedule: occurs every 1 week(s) on Sunday, at 1:00 am

Integrity Tab:
Check database integrity (box checked)
Include indexes (box checked)
Attempt to repair any minor problems (was checked, now
unchecked due to bug)
Perform these tests before backing up the database or
transaction log (box checked)
Schedule: occurs every 1 week(s) on Sunday at 12:00:00 AM

Complete Backup Tab:
Backup the database as part of the Maintenance Plan (box
checked)
Verify the integrity of the backup upon completion (box
checked)
Disk (box was checked, now Tape box checked instead)
Use this directory: (was C:\DBBackup, now \\.\Tape0)
Create a subdirectory for each database (box checked)
Remove files older than 1 Day(s)
Backup file extension: .bak
Schedule occurs every 1 week(s) on Sunday at 2:00:00 pm

Transaction Log Backup Tab:
Backup the transaction log of the database as part of the
maintenance plan (box checked)
Verify the integrity of the backup upon completion (box
checked)
Disk (box was checked, now Tape box checked instead)
Use this directory: (was C:\DBBackup, now \\.\Tape0)
Create a subdirectory for each database (box checked)
Remove files older than 1 Day(s)
Backup file extension: .TRN
Schedule occurs every 1 week(s) on M, Tues,Wed, Thurs,
Fri, Sat at 12:00:00 am

1. Could I just run the aforementioned (under the
Optimization, Integrity, Complete Backup, and Transaction
Log Backup Tabs)ahead of schedule to shrink the database,
rather than manually do it? I worry that doing so will
cause a system crash because when they were run last week
our hard drive space reduced from 1.60GB to about 100MB
because the autoloader was malfunctioning - everything
went to disk. Now the autoloader is working, so if set to
tape hopefully, we will be ok? What happens if the
autoloader fails, do the processes fail or go to disk?

As you can see clearing the log and shrinking are already
set in the DB Maintenance Plan, and I believe they are
working satisfactorily. However, now that we are using
tape (\\.\Tape0) instead of disk (C:\DBBackup), if I'm
correct, then why is our server hard drive space still
staying at 65MB?

Quote:
>-----Original Message-----
>Answer# 1.  The log did likely clear.  It did not

shrink.  Shrinking the log
Quote:
>is a manual task.  See BOL for details on exactly how to
do so.
>Answer# 2.  See Answer# 1
>Answer# 3.  Again, clearing the unused portion of the log
and shrinking the
>log are different tasks.  I suggest reading the entire

section on backup and
Quote:
>restore in BOL.  Backups are THE primary responsibility
for a DBA.
>Answer# 4.  Read quickly. :)

>Seriously, there is a standard procedure that is well

documented in BOL on
Quote:
>how to fix this.  I usually set an alert for 'near full
log' that triggers
>an alert.  That won't show autoexpand, but can give you
some idea of how
>often to schedule backups and when to watch the
filesystem.

>--
>Geoff N. Hiten
>Senior Database Administrator
>Careerbuilder.com


message

>> Problem:
>> Using WIN 2K Server, running MS SQL Server 2K and only
>> have 68MB hard drive space left on server. A previous sa
>> failed to check the daily db maintenance plan backups,
let
>> the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
>> Log file grow excessively  which has now used most of
our
>> hard drive space. We only have a 34GB drive. To add
insult
>> to injury, the autoloader was not backing up correctly,
>> but it now works ok. Backed up the aforementioned
>> Transaction Log last night to tape + the entire db, but
>> that process did not clear the Transaction Log, it is
>> still at 26.1GB. Perhaps, the bu did not work correctly
>> because of low hard drive space? I'll check the event
logs
>> shortly.
>> Question # 1: Why did the Transaction Log not clear
after
>> the tape backup?
>> Question # 2: Is there a quick fix to regain sufficient
>> hard drive space without causing db or Transaction Log
>> problems?
>> Question # 3: Is there a manual process to clear the
>> Transaction Log, if so how does it work?
>> Question # 4: Any quick recommendations, fixes to avoid
a
>> system crash because of insufficient hard drive disk
space
>> on the server? We are currently receiving event log
>> warnings to delete files (already completed what we
could)
>> because of our hard drive is near capacity.
>> Please respond quickly as we are not far from a system
>> crash. Thanks for any help.

>> Mike

>.



Sun, 02 Oct 2005 18:38:53 GMT
 Low hard drive space on WIN 2K Server
Edgardo,
Thanks for the quick reply. Yes, please send the script,
and note my latest reply to Geoff for additional
understanding of our situation. Thanks again, and any
additional suggestions would be appreciated.
Mike
Quote:
>-----Original Message-----
>Mike,

>I received your e-mail, however hotmail has a limit of
>messages I can send in a 24HR period, so it didn't send
>the response.....

>Question #1: The transaction log be cleared after the
>backup, however the space allocated remains the same
unles
>you execute the DBCC SHRINKFILE command for the
>transaction log file.

>Question #2: Shrinking the file periodically will help
you
>to re-gain space

>Question #3: I would suggest to use the Database
>Maintenance Plans first, or use the xp_sqlmaint extended
>stored procedure. You can create a job that executes the
>transaction log file backup on the first step and the
DBCC
>SHRINKFILE on the second step to release the space the
>file took to allocate all the transactions.

>Question #4: I have a stored procedure that checks for
>space usage on all my servers. I have a job that executes
>that procedure periodically and alerts me if the drive
>space is less than the amount set. I can send you the
>script if you are interested.

>>-----Original Message-----
>>Problem:
>>Using WIN 2K Server, running MS SQL Server 2K and only
>>have 68MB hard drive space left on server. A previous sa
>>failed to check the daily db maintenance plan backups,
>let
>>the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
>>Log file grow excessively  which has now used most of
our
>>hard drive space. We only have a 34GB drive. To add
>insult
>>to injury, the autoloader was not backing up correctly,
>>but it now works ok. Backed up the aforementioned
>>Transaction Log last night to tape + the entire db, but
>>that process did not clear the Transaction Log, it is
>>still at 26.1GB. Perhaps, the bu did not work correctly
>>because of low hard drive space? I'll check the event
>logs
>>shortly.
>>Question # 1: Why did the Transaction Log not clear
after
>>the tape backup?
>>Question # 2: Is there a quick fix to regain sufficient
>>hard drive space without causing db or Transaction Log
>>problems?
>>Question # 3: Is there a manual process to clear the
>>Transaction Log, if so how does it work?
>>Question # 4: Any quick recommendations, fixes to avoid
a
>>system crash because of insufficient hard drive disk
>space
>>on the server? We are currently receiving event log
>>warnings to delete files (already completed what we
>could)
>>because of our hard drive is near capacity.
>>Please respond quickly as we are not far from a system
>>crash. Thanks for any help.

>>Mike
>>.

>.



Sun, 02 Oct 2005 18:58:41 GMT
 Low hard drive space on WIN 2K Server
Correction to my previous post to Geoff:
Should read as follows under:

Complete Backup Tab:
Schedule occurs every 1 week(s) on Sunday at 2:00:00 am
(not pm as previously posted)

Mike

Quote:
>-----Original Message-----
>Geoff,

>Thanks very much for your help.

>The system is set as follows:
>DB maintenance Plan 1

>Optimization Tab:
>Reorganize data and index pages (box checked)
>Change Free Space per page percentage to: 10%
>Remove unused space from database files (box checked)
>Shrink databse when it grows beyond: 50MB
>Amount of free space to remain after shrink: 10% of the
>data space
>Schedule: occurs every 1 week(s) on Sunday, at 1:00 am

>Integrity Tab:
>Check database integrity (box checked)
>Include indexes (box checked)
>Attempt to repair any minor problems (was checked, now
>unchecked due to bug)
>Perform these tests before backing up the database or
>transaction log (box checked)
>Schedule: occurs every 1 week(s) on Sunday at 12:00:00 AM

>Complete Backup Tab:
>Backup the database as part of the Maintenance Plan (box
>checked)
>Verify the integrity of the backup upon completion (box
>checked)
>Disk (box was checked, now Tape box checked instead)
>Use this directory: (was C:\DBBackup, now \\.\Tape0)
>Create a subdirectory for each database (box checked)
>Remove files older than 1 Day(s)
>Backup file extension: .bak
>Schedule occurs every 1 week(s) on Sunday at 2:00:00 pm

>Transaction Log Backup Tab:
>Backup the transaction log of the database as part of the
>maintenance plan (box checked)
>Verify the integrity of the backup upon completion (box
>checked)
>Disk (box was checked, now Tape box checked instead)
>Use this directory: (was C:\DBBackup, now \\.\Tape0)
>Create a subdirectory for each database (box checked)
>Remove files older than 1 Day(s)
>Backup file extension: .TRN
>Schedule occurs every 1 week(s) on M, Tues,Wed, Thurs,
>Fri, Sat at 12:00:00 am

>1. Could I just run the aforementioned (under the
>Optimization, Integrity, Complete Backup, and Transaction
>Log Backup Tabs)ahead of schedule to shrink the database,
>rather than manually do it? I worry that doing so will
>cause a system crash because when they were run last week
>our hard drive space reduced from 1.60GB to about 100MB
>because the autoloader was malfunctioning - everything
>went to disk. Now the autoloader is working, so if set to
>tape hopefully, we will be ok? What happens if the
>autoloader fails, do the processes fail or go to disk?

>As you can see clearing the log and shrinking are already
>set in the DB Maintenance Plan, and I believe they are
>working satisfactorily. However, now that we are using
>tape (\\.\Tape0) instead of disk (C:\DBBackup), if I'm
>correct, then why is our server hard drive space still
>staying at 65MB?

>>-----Original Message-----
>>Answer# 1.  The log did likely clear.  It did not
>shrink.  Shrinking the log
>>is a manual task.  See BOL for details on exactly how to
>do so.
>>Answer# 2.  See Answer# 1
>>Answer# 3.  Again, clearing the unused portion of the
log
>and shrinking the
>>log are different tasks.  I suggest reading the entire
>section on backup and
>>restore in BOL.  Backups are THE primary responsibility
>for a DBA.
>>Answer# 4.  Read quickly. :)

>>Seriously, there is a standard procedure that is well
>documented in BOL on
>>how to fix this.  I usually set an alert for 'near full
>log' that triggers
>>an alert.  That won't show autoexpand, but can give you
>some idea of how
>>often to schedule backups and when to watch the
>filesystem.

>>--
>>Geoff N. Hiten
>>Senior Database Administrator
>>Careerbuilder.com


>message

>>> Problem:
>>> Using WIN 2K Server, running MS SQL Server 2K and only
>>> have 68MB hard drive space left on server. A previous
sa
>>> failed to check the daily db maintenance plan backups,
>let
>>> the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
>>> Log file grow excessively  which has now used most of
>our
>>> hard drive space. We only have a 34GB drive. To add
>insult
>>> to injury, the autoloader was not backing up correctly,
>>> but it now works ok. Backed up the aforementioned
>>> Transaction Log last night to tape + the entire db, but
>>> that process did not clear the Transaction Log, it is
>>> still at 26.1GB. Perhaps, the bu did not work correctly
>>> because of low hard drive space? I'll check the event
>logs
>>> shortly.
>>> Question # 1: Why did the Transaction Log not clear
>after
>>> the tape backup?
>>> Question # 2: Is there a quick fix to regain sufficient
>>> hard drive space without causing db or Transaction Log
>>> problems?
>>> Question # 3: Is there a manual process to clear the
>>> Transaction Log, if so how does it work?
>>> Question # 4: Any quick recommendations, fixes to
avoid
>a
>>> system crash because of insufficient hard drive disk
>space
>>> on the server? We are currently receiving event log
>>> warnings to delete files (already completed what we
>could)
>>> because of our hard drive is near capacity.
>>> Please respond quickly as we are not far from a system
>>> crash. Thanks for any help.

>>> Mike

>>.

>.



Sun, 02 Oct 2005 19:04:01 GMT
 Low hard drive space on WIN 2K Server
Comments Inline

Quote:
> Geoff,

> Thanks very much for your help.

> The system is set as follows:
> DB maintenance Plan 1

Hope this is a very low activity time
Quote:

> Optimization Tab:
> Reorganize data and index pages (box checked)
> Change Free Space per page percentage to: 10%

This will take much log space.  Consider writing your own scripts using DBCC
INDEXDEFRAG.
Quote:
> Remove unused space from database files (box checked)

Not a good idea, it will cause you performance to take a big hit when the
database has to expand, usually during a high activity time.
Quote:
> Shrink databse when it grows beyond: 50MB
> Amount of free space to remain after shrink: 10% of the
> data space
> Schedule: occurs every 1 week(s) on Sunday, at 1:00 am

> Integrity Tab:
> Check database integrity (box checked) Good
> Include indexes (box checked) Again, Good.
> Attempt to repair any minor problems (was checked, now
> unchecked due to bug) Definitely correct.  It will take the DB to

single-user mode for fixes and sometimes not put it back.
Quote:
> Perform these tests before backing up the database or
> transaction log (box checked)
> Schedule: occurs every 1 week(s) on Sunday at 12:00:00 AM

> Complete Backup Tab:
> Backup the database as part of the Maintenance Plan (box
> checked)
> Verify the integrity of the backup upon completion (box
> checked)
> Disk (box was checked, now Tape box checked instead)
> Use this directory: (was C:\DBBackup, now \\.\Tape0)
> Create a subdirectory for each database (box checked)
> Remove files older than 1 Day(s)
> Backup file extension: .bak
> Schedule occurs every 1 week(s) on Sunday at 2:00:00 pm

Good.  Make sure the first task finishes before starting this one.
Quote:

> Transaction Log Backup Tab:
> Backup the transaction log of the database as part of the
> maintenance plan (box checked)
> Verify the integrity of the backup upon completion (box
> checked)
> Disk (box was checked, now Tape box checked instead)

I like writing my log backups to a file share on another computer and then
to tape if desired.  Personal preference thing.  You can use an older system
with cheaper (ATA?) drives instead of high-speed data drives.
Quote:
> Use this directory: (was C:\DBBackup, now \\.\Tape0)
> Create a subdirectory for each database (box checked)
> Remove files older than 1 Day(s)  Usually, I like two to four weeks.

Regardless, you need to keep it for two full backup cycles (in case one
backup is bad)
Quote:
> Backup file extension: .TRN
> Schedule occurs every 1 week(s) on M, Tues,Wed, Thurs,
> Fri, Sat at 12:00:00 am

More frequent log backups won't hurt.  One trick is to create an alert for
near full log that fires off the log backup job.  That way, it can reduce
the need to expand the log.
Quote:

> 1. Could I just run the aforementioned (under the
> Optimization, Integrity, Complete Backup, and Transaction
> Log Backup Tabs)ahead of schedule to shrink the database,
> rather than manually do it? I worry that doing so will
> cause a system crash because when they were run last week
> our hard drive space reduced from 1.60GB to about 100MB
> because the autoloader was malfunctioning - everything
> went to disk. Now the autoloader is working, so if set to
> tape hopefully, we will be ok? What happens if the
> autoloader fails, do the processes fail or go to disk?

The processes should fail if your autoloader fails.  You can run the
maintenance plan, but I would run the shrink commands manually.
Quote:

> As you can see clearing the log and shrinking are already
> set in the DB Maintenance Plan, and I believe they are
> working satisfactorily. However, now that we are using
> tape (\\.\Tape0) instead of disk (C:\DBBackup), if I'm
> correct, then why is our server hard drive space still
> staying at 65MB?

> >-----Original Message-----
> >Answer# 1.  The log did likely clear.  It did not
> shrink.  Shrinking the log
> >is a manual task.  See BOL for details on exactly how to
> do so.
> >Answer# 2.  See Answer# 1
> >Answer# 3.  Again, clearing the unused portion of the log
> and shrinking the
> >log are different tasks.  I suggest reading the entire
> section on backup and
> >restore in BOL.  Backups are THE primary responsibility
> for a DBA.
> >Answer# 4.  Read quickly. :)

> >Seriously, there is a standard procedure that is well
> documented in BOL on
> >how to fix this.  I usually set an alert for 'near full
> log' that triggers
> >an alert.  That won't show autoexpand, but can give you
> some idea of how
> >often to schedule backups and when to watch the
> filesystem.

> >--
> >Geoff N. Hiten
> >Senior Database Administrator
> >Careerbuilder.com


> message

> >> Problem:
> >> Using WIN 2K Server, running MS SQL Server 2K and only
> >> have 68MB hard drive space left on server. A previous sa
> >> failed to check the daily db maintenance plan backups,
> let
> >> the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
> >> Log file grow excessively  which has now used most of
> our
> >> hard drive space. We only have a 34GB drive. To add
> insult
> >> to injury, the autoloader was not backing up correctly,
> >> but it now works ok. Backed up the aforementioned
> >> Transaction Log last night to tape + the entire db, but
> >> that process did not clear the Transaction Log, it is
> >> still at 26.1GB. Perhaps, the bu did not work correctly
> >> because of low hard drive space? I'll check the event
> logs
> >> shortly.
> >> Question # 1: Why did the Transaction Log not clear
> after
> >> the tape backup?
> >> Question # 2: Is there a quick fix to regain sufficient
> >> hard drive space without causing db or Transaction Log
> >> problems?
> >> Question # 3: Is there a manual process to clear the
> >> Transaction Log, if so how does it work?
> >> Question # 4: Any quick recommendations, fixes to avoid
> a
> >> system crash because of insufficient hard drive disk
> space
> >> on the server? We are currently receiving event log
> >> warnings to delete files (already completed what we
> could)
> >> because of our hard drive is near capacity.
> >> Please respond quickly as we are not far from a system
> >> crash. Thanks for any help.

> >> Mike

> >.



Sun, 02 Oct 2005 19:35:37 GMT
 Low hard drive space on WIN 2K Server
Here is the code for the disk space alert stored
procedure. <u><b>Please make sure you test it in a TEST
SERVER FIRST before moving it to the production
server</b></u>


AS
/*

        This procedure will send a notification if the
free disk space on any of the drives where the
        SQL Server resides on is lower than the specified
limit. The alert can is via email and netsend.

        usage:
                        exec master.dbo.usp_diskalert 1000

        This will send an email and a netsend if the free
disk space is less than 1000mb

        Requires SQL Mail.

        If xp_cmdshell doesn't exist it will be added and
dropped as needed

        More than one email address can be specified,
separate using semi colons

*/

BEGIN
SET NOCOUNT ON





CREATE TABLE #T1(
        DRVLETTER CHAR(1),
        DRVSPACE INT
        )

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives

/* GENERATE THE MESSAGE */


CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN




GET FIRST DRIVE LETTER

                        ORDER BY DRVLETTER ASC)


DISK SPACE FOR THE LETTER



                        + 'MB' + CHAR(13) + CHAR(10)


        BEGIN                                   --LOOP
THROUGH DRIVE LETTERS AND REPEAT ABOVE          

FROM #T1


                                ORDER BY DRVLETTER ASC)





                                + CHAR(13) + CHAR(10)
        END

        /* SEND THE MESSAGE */

        --SEND EMAIL
        BEGIN



+ ''',

+ ''',



        END

         -- NET SEND
        BEGIN  
                --DETERMINE IF XP_CMDSHELL EXISTS


                IF NOT EXISTS(SELECT NAME FROM
master..sysobjects WHERE NAME = 'XP_CMDSHELL')

                --IF NOT RECREATE IT

                BEGIN
                        EXEC
sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
                        PRINT 'ADDING XP_CMDSHELL'
                END

                --NET SEND MSG







                --DROP XP_CMDSHELL IF IT DIDN'T EXIST

                BEGIN
                        EXEC
sp_dropextendedproc 'xp_cmdshell'
                        PRINT 'DROPPING XP_CMDSHELL'
                END
        END
END
/* CLEANUP */
DROP TABLE #T1
END
GO

Quote:
>-----Original Message-----
>Edgardo,
>Thanks for the quick reply. Yes, please send the script,
>and note my latest reply to Geoff for additional
>understanding of our situation. Thanks again, and any
>additional suggestions would be appreciated.
>Mike
>>-----Original Message-----
>>Mike,

>>I received your e-mail, however hotmail has a limit of
>>messages I can send in a 24HR period, so it didn't send
>>the response.....

>>Question #1: The transaction log be cleared after the
>>backup, however the space allocated remains the same
>unles
>>you execute the DBCC SHRINKFILE command for the
>>transaction log file.

>>Question #2: Shrinking the file periodically will help
>you
>>to re-gain space

>>Question #3: I would suggest to use the Database
>>Maintenance Plans first, or use the xp_sqlmaint extended
>>stored procedure. You can create a job that executes the
>>transaction log file backup on the first step and the
>DBCC
>>SHRINKFILE on the second step to release the space the
>>file took to allocate all the transactions.

>>Question #4: I have a stored procedure that checks for
>>space usage on all my servers. I have a job that
executes
>>that procedure periodically and alerts me if the drive
>>space is less than the amount set. I can send you the
>>script if you are interested.

>>>-----Original Message-----
>>>Problem:
>>>Using WIN 2K Server, running MS SQL Server 2K and only
>>>have 68MB hard drive space left on server. A previous
sa
>>>failed to check the daily db maintenance plan backups,
>>let
>>>the GPSFRFLog.ldf (now 26.1GB and growing)- Transaction
>>>Log file grow excessively  which has now used most of
>our
>>>hard drive space. We only have a 34GB drive. To add
>>insult
>>>to injury, the autoloader was not backing up correctly,
>>>but it now works ok. Backed up the aforementioned
>>>Transaction Log last night to tape + the entire db, but
>>>that process did not clear the Transaction Log, it is
>>>still at 26.1GB. Perhaps, the bu did not work correctly
>>>because of low hard drive space? I'll check the event
>>logs
>>>shortly.
>>>Question # 1: Why did the Transaction Log not clear
>after
>>>the tape backup?
>>>Question # 2: Is there a quick fix to regain sufficient
>>>hard drive space without causing db or Transaction Log
>>>problems?
>>>Question # 3: Is there a manual process to clear the
>>>Transaction Log, if so how does it work?
>>>Question # 4: Any quick recommendations, fixes to avoid
>a
>>>system crash because of insufficient hard drive disk
>>space
>>>on the server? We are currently receiving event log
>>>warnings to delete files (already completed what we
>>could)
>>>because of our hard drive is near capacity.
>>>Please respond quickly as we are not far from a system
>>>crash. Thanks for any help.

>>>Mike
>>>.

>>.

>.



Sun, 02 Oct 2005 19:48:35 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Client on 1 hard drive and server on other hard drive

2. Wrong number displayed for Free space on hard drive when installing SQL SErver

3. Unable to install SQL 7 on Win 2K proffessional and Win 2K server both

4. how can sql mail (on 6.5) notify of low hard disk free space

5. restoring SQL 2k datafile to a new hard drive for ARC GIS and ARC IMS

6. SQL Server 2K on Win 2k Server DSN issue

7. URGENT: HELP: DB2 UDB v6.1 Hard Drive Space Problem

8. MSSQL 7 underreporting hard drive free space by many gigs

9. Install does not see free space on hard drive

10. need hard drive space!!

11. Hard Drive Storage Space

12. Cube Processing causes tempdb database to take all hard drive space


 
Powered by phpBB® Forum Software