Monday, March 19, 2012

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GORob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance plan
>> wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
>> Thanks Uri,
>> But I still need an interpreter...
>> Left unchecked, the Log file appears to grow and fill up space on the
>> server. My assumption, is that IF a good full backup has been
>> successful, then it should be OK to use SHRINKFILE to lose the
>> transaction file. At that point, there would be no need to restore a
>> transaction log file...
>> BTW - I never use shrinkdatabase
>> Rob
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>>
>|||i need to creat a log.ldf file . i am only having the Data.Mdf file with me. I does'nt want to attach the database but i want to only create the log.ldf file how can i creat it.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

No comments:

Post a Comment