DENY permission,SQL server: file

Showing posts with label file. Show all posts

Thursday, March 29, 2012

Deleting records in the logfile

I have a database that is used to store a lot of data. We load the data on a
daily basis, several thousand records per day. The Log file is not needed,
so whats the best way to delete the records in it and reduce the size

Thanks

Derrick"Derrick King" <derrick.king@.bradford.gov.uk> wrote in message
news:c1l7ag$ejl$1@.newsreaderm1.core.theplanet.net. ..
> I have a database that is used to store a lot of data. We load the data on
a
> daily basis, several thousand records per day. The Log file is not needed,
> so whats the best way to delete the records in it and reduce the size
> Thanks
> Derrick

You don't mention which version of MSSQL you have, but assuming it's 2000,
then see "Recovery Models" in Books Online. If you don't need transaction
log backups, the easiest solution is probably to set the database to Simple
recovery mode, which will automatically recover log space if possible.

If that's not acceptable, then you can consider transaction log backups (if
you don't already do that), which will truncate the log. Truncating the log
frees up log space but does not make it physically smaller, so you may also
need to use DBCC SHRINKFILE - see "Shrinking Databases".

Simon

Tuesday, March 27, 2012

Deleting or Clearing

Is there a way to delete a database log file without detaching the database?
I don't care about loosing the ability to roll back, etc.
I tried "restricting the growth" setting, but that just makes the database
unusable when it hits the limit. I'd like to be able to restrict a log file
from growing above a specific amount.No, SQL Server need the log file. First you need to consider recovery model.
If you have full, you
need to do regular log backups to empty (not shrink) the log. If you don't w
ant to do regular log
backup (which has a bunch of advantages), then have simple recovery model. O
nce you have sorted that
out, see http://www.karaszi.com/SQLServer/info_dont_shrink.asp for info on h
ow to do the actual
shrink of the log files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.g
bl...
> Is there a way to delete a database log file without detaching the databas
e? I don't care about
> loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
unusable when it hits
> the limit. I'd like to be able to restrict a log file from growing above a
specific amount.
>|||The key point here is to use "dbcc shrinkdatabase".
The first solution keeps the backup of data and transaction logs.
The database log file CANNOT be removed, but the size will be reduced.
Depending on your database size, this may be time consuming due to the
backup task.
- Do a full backup on the database.
- Shrink the database using "dbcc shrinkdatabase (dbname)".
Here's another quick trick that I will use when there is not enough space on
the server for doing a full/log backup, while adding a HDD is not possible.
(But, the data in the log database will be removed, and cannot be
recovered!)
-- ** warning ** (copied from BOL)
-- TRUNCATE_ONLY removes the inactive part of the log without making a
backup copy of it and truncates the log.
-- This option frees space. Specifying a backup device is unnecessary
because the log backup is not saved.
-- The changes recorded in the log are not recoverable.
-- For recovery purposes, immediately execute BACKUP DATABASE.
backup log dbname with truncate_only
-- You can then shrink the database.
dbcc shrinkdatabase (dbname)
Martin C K Poon
Senior Analyst Programmer
====================================
"Scott" <sbailey@.mileslumber.com> bl
news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> Is there a way to delete a database log file without detaching the
database?
> I don't care about loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
> unusable when it hits the limit. I'd like to be able to restrict a log
file
> from growing above a specific amount.
>|||so, to your knowledge, this method will reduce a log the maximum amount that
is possible while keeping the database attached?
"Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
> The key point here is to use "dbcc shrinkdatabase".
> The first solution keeps the backup of data and transaction logs.
> The database log file CANNOT be removed, but the size will be reduced.
> Depending on your database size, this may be time consuming due to the
> backup task.
> - Do a full backup on the database.
> - Shrink the database using "dbcc shrinkdatabase (dbname)".
> Here's another quick trick that I will use when there is not enough space
> on
> the server for doing a full/log backup, while adding a HDD is not
> possible.
> (But, the data in the log database will be removed, and cannot be
> recovered!)
> -- ** warning ** (copied from BOL)
> -- TRUNCATE_ONLY removes the inactive part of the log without making a
> backup copy of it and truncates the log.
> -- This option frees space. Specifying a backup device is unnecessary
> because the log backup is not saved.
> -- The changes recorded in the log are not recoverable.
> -- For recovery purposes, immediately execute BACKUP DATABASE.
> backup log dbname with truncate_only
> -- You can then shrink the database.
> dbcc shrinkdatabase (dbname)
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Scott" <sbailey@.mileslumber.com> bl
> news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> database?
> file
>|||Both backup (full/log) and "dbcc shrinkdatabase" can be done with the
database online (and without detaching the database).
There is also a database option 'autoshrink' that could be used for
shrinking a database periodically and automcatically by SQL Server. By
default, the 'autoshrink' option is set to OFF in SS2000 (except SS2000
Personal Edition). You will need to implement an appropriate backup
strategy, anyways.
-- To set the autoshrink database option. (When true, the database files are
candidates for automatic periodic shrinking.)
sp_dboption 'dbname', 'autoshrink', 'TRUE/FALSE'
References
- Shrinking the transaction log
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
- Truncating the transaction log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"scott" <sbailey@.mileslumber.com> bl
news:%23E1ZEXgdGHA.1260@.TK2MSFTNGP05.phx.gbl g...
> so, to your knowledge, this method will reduce a log the maximum amount
that
> is possible while keeping the database attached?
>
> "Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
> message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
space
>sql

Deleting Old .BAK file Problem

Hello,
My Backup Maintenance Plan is scheduled to delete old
backup (.BAK) files but for some odd reason it cannot
delete two old files. When i go in to delete them manually
or check the security settings ,but give me an access
denied prompt. I am logged in as Administrator w/ full
rights, but only these files are giving me denied access.
Please help
Thanks in advance
ATHi,
Stop SQL Agent service and try deleting the file.
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
> Hello,
> My Backup Maintenance Plan is scheduled to delete old
> backup (.BAK) files but for some odd reason it cannot
> delete two old files. When i go in to delete them manually
> or check the security settings ,but give me an access
> denied prompt. I am logged in as Administrator w/ full
> rights, but only these files are giving me denied access.
> Please help
> Thanks in advance
> AT|||thanks hari, are there any contingencies incase this does
net work?
thanks
AT
>--Original Message--
>Hi,
>Stop SQL Agent service and try deleting the file.
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
>> Hello,
>> My Backup Maintenance Plan is scheduled to delete old
>> backup (.BAK) files but for some odd reason it cannot
>> delete two old files. When i go in to delete them
manually
>> or check the security settings ,but give me an access
>> denied prompt. I am logged in as Administrator w/ full
>> rights, but only these files are giving me denied
access.
>> Please help
>> Thanks in advance
>> AT
>
>.
>|||Hi,
Incase if this fail, check any other process uses this file. Mostly it will
be some backup processes (tape / disk copy).
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
> thanks hari, are there any contingencies incase this does
> net work?
> thanks
> AT
> >--Original Message--
> >Hi,
> >
> >Stop SQL Agent service and try deleting the file.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"AT" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> My Backup Maintenance Plan is scheduled to delete old
> >> backup (.BAK) files but for some odd reason it cannot
> >> delete two old files. When i go in to delete them
> manually
> >> or check the security settings ,but give me an access
> >> denied prompt. I am logged in as Administrator w/ full
> >> rights, but only these files are giving me denied
> access.
> >> Please help
> >>
> >> Thanks in advance
> >>
> >> AT
> >
> >
> >.
> >|||Hey thanks Hari for the replies...
But I was able to resolve the problem by doing a simple
sys reboot..
thanks again for your help..
AT
>--Original Message--
>Hi,
>Incase if this fail, check any other process uses this
file. Mostly it will
>be some backup processes (tape / disk copy).
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
>> thanks hari, are there any contingencies incase this
does
>> net work?
>> thanks
>> AT
>> >--Original Message--
>> >Hi,
>> >
>> >Stop SQL Agent service and try deleting the file.
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >"AT" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> My Backup Maintenance Plan is scheduled to delete old
>> >> backup (.BAK) files but for some odd reason it cannot
>> >> delete two old files. When i go in to delete them
>> manually
>> >> or check the security settings ,but give me an access
>> >> denied prompt. I am logged in as Administrator w/
full
>> >> rights, but only these files are giving me denied
>> access.
>> >> Please help
>> >>
>> >> Thanks in advance
>> >>
>> >> AT
>> >
>> >
>> >.
>> >
>
>.
>

Deleting Old .BAK file Problem

Sunday, March 25, 2012

Deleting log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.|||Deleting your log file will kill your database, so its
probably not a good thing to do ;)
Instead either shrink it, or change the SQL so it
immeditatly commits to disk,
J
>--Original Message--
>Hi ,
>I have a huge sql server 2000 database. Everyday I am
importing some
>records into it. The .mdf file size is around 20 GB.
Surprisingly the
>..ldf file size is increasing rapidly and it has reached
50 GB.
>I want to import lot more records and there is a
possibility that I
>may run out of disk space.
>Please let me know if I can delete this log file. I can
always detach
>the database -delete the log file and reattach the
database again. But
>the question is- Is it safe to do ? Also what is the
possibility of
>database not getting attached properly and data loss ?
any guesses.
>Thanks in advance,
>Vardhan.
>.
>|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it and
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediately
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
> > Hi ,
> > I have a huge sql server 2000 database. Everyday I am importing some
> > records into it. The .mdf file size is around 20 GB. Surprisingly the
> > .ldf file size is increasing rapidly and it has reached 50 GB.
> >
> > I want to import lot more records and there is a possibility that I
> > may run out of disk space.
> >
> > Please let me know if I can delete this log file. I can always detach
> > the database -delete the log file and reattach the database again. But
> > the question is- Is it safe to do ? Also what is the possibility of
> > database not getting attached properly and data loss ? any guesses.
> >
> > Thanks in advance,
> > Vardhan.

Deleting log file in sql 2000 database

quote:

> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.

|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/d... />
t_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...[Q
UOTE]
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it an
d
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediatel
y
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...

Deleting Log file

Hello
Is there any time / way /procedure to delte the log file and let SQL
recreate it without losing any data. In other words - at what point in time
can one be safely assured that all data in the log file is committed to the
database.
reason - have 1.2gb database and 9gb logfile - caused by duplicating
another database ( importing thru ODBC ) each night and would like to kill
the log file each morning after the transfer - the SQL database is only
used for reporting as the other package has lots of limitations - yep - we
are working on converting it all to SQL so this replication doesn't have to
happen but that is still a fair way off
TIA
PeteIf your recovery plan is to restore from your last full database backup (or
rerun your import), you can set the database recovery model to SIMPLE so
that committed transactions are automatically removed from the log.
You can run DBCC SHRINKFILE to release unused log space back to the OS. The
log will still need to be large enough to accommodate your largest
transaction so you probably don't want to do this routinely.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pete" <p0911@.hotmail.com> wrote in message
news:eAMZK0PSEHA.1568@.TK2MSFTNGP11.phx.gbl...
> Hello
> Is there any time / way /procedure to delte the log file and let SQL
> recreate it without losing any data. In other words - at what point in
time
> can one be safely assured that all data in the log file is committed to
the
> database.
> reason - have 1.2gb database and 9gb logfile - caused by duplicating
> another database ( importing thru ODBC ) each night and would like to kill
> the log file each morning after the transfer - the SQL database is only
> used for reporting as the other package has lots of limitations - yep - we
> are working on converting it all to SQL so this replication doesn't have
to
> happen but that is still a fair way off
> TIA
> Pete
>sql

Deleting Log file

Hello
Is there any time / way /procedure to delte the log file and let SQL
recreate it without losing any data. In other words - at what point in time
can one be safely assured that all data in the log file is committed to the
database.
reason - have 1.2gb database and 9gb logfile - caused by duplicating
another database ( importing thru ODBC ) each night and would like to kill
the log file each morning after the transfer - the SQL database is only
used for reporting as the other package has lots of limitations - yep - we
are working on converting it all to SQL so this replication doesn't have to
happen but that is still a fair way off
TIA
Pete
If your recovery plan is to restore from your last full database backup (or
rerun your import), you can set the database recovery model to SIMPLE so
that committed transactions are automatically removed from the log.
You can run DBCC SHRINKFILE to release unused log space back to the OS. The
log will still need to be large enough to accommodate your largest
transaction so you probably don't want to do this routinely.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pete" <p0911@.hotmail.com> wrote in message
news:eAMZK0PSEHA.1568@.TK2MSFTNGP11.phx.gbl...
> Hello
> Is there any time / way /procedure to delte the log file and let SQL
> recreate it without losing any data. In other words - at what point in
time
> can one be safely assured that all data in the log file is committed to
the
> database.
> reason - have 1.2gb database and 9gb logfile - caused by duplicating
> another database ( importing thru ODBC ) each night and would like to kill
> the log file each morning after the transfer - the SQL database is only
> used for reporting as the other package has lots of limitations - yep - we
> are working on converting it all to SQL so this replication doesn't have
to
> happen but that is still a fair way off
> TIA
> Pete
>

Deleting Log file

Hello
Is there any time / way /procedure to delte the log file and let SQL
recreate it without losing any data. In other words - at what point in time
can one be safely assured that all data in the log file is committed to the
database.
reason - have 1.2gb database and 9gb logfile - caused by duplicating
another database ( importing thru ODBC ) each night and would like to kill
the log file each morning after the transfer - the SQL database is only
used for reporting as the other package has lots of limitations - yep - we
are working on converting it all to SQL so this replication doesn't have to
happen but that is still a fair way off
TIA
PeteIf your recovery plan is to restore from your last full database backup (or
rerun your import), you can set the database recovery model to SIMPLE so
that committed transactions are automatically removed from the log.
You can run DBCC SHRINKFILE to release unused log space back to the OS. The
log will still need to be large enough to accommodate your largest
transaction so you probably don't want to do this routinely.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pete" <p0911@.hotmail.com> wrote in message
news:eAMZK0PSEHA.1568@.TK2MSFTNGP11.phx.gbl...
> Hello
> Is there any time / way /procedure to delte the log file and let SQL
> recreate it without losing any data. In other words - at what point in
time
> can one be safely assured that all data in the log file is committed to
the
> database.
> reason - have 1.2gb database and 9gb logfile - caused by duplicating
> another database ( importing thru ODBC ) each night and would like to kill
> the log file each morning after the transfer - the SQL database is only
> used for reporting as the other package has lots of limitations - yep - we
> are working on converting it all to SQL so this replication doesn't have
to
> happen but that is still a fair way off
> TIA
> Pete
>

Deleting lines from a text file

I have a flat file that look like this

############################################################
# Maturity Type Lookup

and i want it to look like this

Basically remove any line that start with a "#" or any blank lines..

I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful

Thanks for any help in advance.

smathew

You don't need a script component for that. Use a derived colum to trim leading spaces and then a conditional split to discard all rows that start with '#'|||

but when it reads the file.. I am assuming you have to use a Flat File Source...

in that case all the lines are read into a single column...

true that you will be able to delete empty lines and lines that start with a #, but you end up having the rest in a single column instead of having 4 coulmns.

|||

You could add a second dataflow where you read the file after removing the '#' rows. This time, the flat file source has 4 columns instead a single one. This is just in case you don't want to use the script component. A drawback is that you need to read the file twice.

|||Given your example, I'd follow Rafael's recommendation. If the file is large, and you don't want to process it twice, take a look at http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx. This has some example script for taking in the flat file and parsing it. You could alter the script slightly to look for and discard the "#" rows.

Deleting Files using SSIS Scripting Object

I am utlizing a scripting object in my ssis to combine two text files into one final file, and then I want to delete the original files. To do this I am utilizing the FileSystemInfo namespace and associating the file names, then utilizing the DELETE functionality.

The creation of the final file works perfectly...unfortunately, my base files do not delete, and I do not get a failure message or indictator.

Here is my code:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.IO.File
Imports System.IO.FileSystemInfo
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strCurrentMonth As String
Dim strCurrentYear As String
Dim strWriteFileName As String
Dim strReadHeaderFileName As String
Dim strReadBodyFileName As String

'Utilizing a case statement, determine the monthname & year and set the appropriate variables

Select Case Month(Now())
Case 1
strCurrentMonth = "January"
Case 2
strCurrentMonth = "February"
Case 3
strCurrentMonth = "March"
Case 4
strCurrentMonth = "April"
Case 5
strCurrentMonth = "May"
Case 6
strCurrentMonth = "June"
Case 7
strCurrentMonth = "July"
Case 8
strCurrentMonth = "August"
Case 9
strCurrentMonth = "September"
Case 10
strCurrentMonth = "October"
Case 11
strCurrentMonth = "November"
Case 12
strCurrentMonth = "December"
End Select

strCurrentYear = Year(Now()).ToString

'Set variables with file names (reader files and write file) for ease in readability and to
'set final (write file) with appropriate nameing convention utilized by Matria HealthCare.

strWriteFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\cup_ref_cup_" & strCurrentMonth & strCurrentYear & "_ftp_ReferralFormat.txt"

strReadHeaderFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral_Control.txt"

strReadBodyFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral.txt"

'create stream reader/writer objects

Dim sr As New StreamReader(strReadHeaderFileName)
Dim sr2 As New StreamReader(strReadBodyFileName)
Dim sw As New StreamWriter(strWriteFileName)

'feed the header record into the final file

Do Until sr.Peek = -1
'write the header record
sw.WriteLine(sr.ReadLine)
Loop

'close the read stream for the header record file
sr.Close()

'Feed the body records into the final file
Do Until sr2.Peek = -1
'write all base records
sw.WriteLine(sr2.ReadLine)
Loop

'close the read stream for the body records
sr2.Close()

'close the write stream for the final distribution file
sw.Close()

'dispose of all stream objects
sr.Dispose()
sr2.Dispose()
sw.Dispose()

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

'final statement for SSIS package to determine script result

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I would appreciate any light you can shed on this. Thanks!

I have also posted this in the Visual Basic Language forum. But, again, any help/guidance would be appreciated.

|||

hi,

My issue going beyond of yours because of I am not be able even to read my file...

Do Until sFitxer.Peek = -1

sFitxer3.WriteLine(sFitxer.ReadLine)

Loop

Dim line As String

line = sFitxer.ReadLine

sFitxer3.WriteLine(line)

Loop Until line Is Nothing

Neither of them works.

any help will be welcomed.

|||

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

The problem is on the first two lines. You are passing strReadBodyFileName and strReadHeaderFileName as string values rather than variables. Remove the quotes around them.

|||

You could always simplify the script you have as well:

File.WriteAllText(varForCombinedFile, File.ReadAllText(strReadHeaderFileName))
File.AppendAllText(varForCombinedFile, File.ReadAllText(strReadBodyFileName))
File.Delete(strReadHeaderFileName)
File.Delete(strReadBodyFileName)

Hope this helps.

|||

Also you can use String.Format("{0:MMMM}", DateTime.Now) to derive the Long month name instead of the Select Case.

Deleting FileGroup/(.ldf) file

Hello, I'm currently busy with Table Partitioning. I have 9 FileGroups, which is all populated with data.

My question is, how do you delete / trucate a FileGroup / file(.ldf) which is already populated?

I tried

ALTER DATABASE ##

but it keeps telling me that the FileGroup cannot be deleted because it is populated.

Any answers would be greatly appreciated.

You have filegroups for the log files?

Thursday, March 22, 2012

Deleting extra tempdb log and data files

We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.

Thanks much.

sean"Sean Lambert" <slambert007@.yahoo.com> wrote in message
news:279f38c0.0309241527.1e7b2546@.posting.google.c om...
> We had someone create an extra data file and log file for tempdb. So
> we currently have two data files and two log files. Is it possible to
> delete the newly created data and log files? If I just delete the
> physical files, I assume they'll get created as soon as SQL Server
> gets started back up. Any help would be great, since a single data
> and log file for tempdb is my goal.

You should be able to simply shut down SQL Server, delete them (back them up
just in case :-) and start it back.

(assuming SQL 2000).

If not, I'd probably try starting SQL Server in single user mode and
removing them there.

But, again, back up everything etc.

> Thanks much.
>
> sean|||slambert007@.yahoo.com (Sean Lambert) wrote in message news:<279f38c0.0309241527.1e7b2546@.posting.google.com>...
> We had someone create an extra data file and log file for tempdb. So
> we currently have two data files and two log files. Is it possible to
> delete the newly created data and log files? If I just delete the
> physical files, I assume they'll get created as soon as SQL Server
> gets started back up. Any help would be great, since a single data
> and log file for tempdb is my goal.
> Thanks much.
>
> sean

Assuming you have SQL2000, then this covers it:

http://support.microsoft.com/?kbid=814576

In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
the files, then ALTER DATABASE ... REMOVE FILE.

Simon|||I'm using SQL7. Sorry...I should have mentioned that initially. Thanks!

sean

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0309250040.1a1813d6@.posting.google.com>...
> slambert007@.yahoo.com (Sean Lambert) wrote in message news:<279f38c0.0309241527.1e7b2546@.posting.google.com>...
> > We had someone create an extra data file and log file for tempdb. So
> > we currently have two data files and two log files. Is it possible to
> > delete the newly created data and log files? If I just delete the
> > physical files, I assume they'll get created as soon as SQL Server
> > gets started back up. Any help would be great, since a single data
> > and log file for tempdb is my goal.
> > Thanks much.
> > sean
> Assuming you have SQL2000, then this covers it:
> http://support.microsoft.com/?kbid=814576
> In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
> the files, then ALTER DATABASE ... REMOVE FILE.
> Simon

Deleting Existing Data before Loading New Data

I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.

I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?

anils wrote:
I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?

In the control flow, use an ExecuteSQL Task to call TRUNCATE (or DELETE FROM) each of the tables you want to purge, before calling the Data Flow task.
|||The load doesn't sound incremental. It sounds like you are doing "drop and replace". If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't.

If you do want to drop and replace and allow rollback if the replace fails, then you need to enable transactions. First create an Execute SQL task(s) to delete your data before the Data Flow that loads the new data. Determine or create a container that will have the same scope as the transaction you want to create. If there are no other tasks in your control flow, then the package can be the container. Otherwise you can add a Sequence Container to hold the tasks that will be part of the transaction. On the container change the TransactionOption to "Required". Make sure it remains the default "Supported" for each task. Now if the Data Flow fails for some reason, the deletes will be rolled back.

|||Thanks JayH for the reply.

"If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"

Could You please explain in detail on how to do this?
|||

anils wrote:
Thanks JayH for the reply.
"If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"
Could You please explain in detail on how to do this?

You can use the Lookup component to find rows that don't exist in your destination table by redirecting the row on a lookup failure. See Method #2 of this article: http://www.sqlis.com/311.aspx

If you need to detect changes and not just new rows, then it becomes trickier and you may find this helpful: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Wednesday, March 21, 2012

Deleting data bloats log file

When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.

Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).

The deletes are recorded in the Transaction Log, so that in case of needing to rebuild a 'crashed' database, you have a complete record or every action.

However, if you are just trying to clean out tables, you 'might' be able to use the TRUNCATE command.

TRUNCATE TABLE MyTable

If you are just removing part of the data, then you could change the database recovery type to 'Simple', and the log will be truncated as soon as the deletes are complete.

|||Hi Arnie,

I just want to create a lightweight version of the DB to make the VB application load and run faster during the early stages of development (and take up less drive space). I want the structure to remain the same so that when the application code is in good shape I will use the full data version. So I want to remove most of the data and keep the file size (especially log) as low as possible.

It sounds like I need to change the recovery type to 'Simple' but don't know how. Have checked out the menus and search through Books Online without luck.
|||

One consideration many of us make is to create a script of the database, perhaps including scripted data inserts for Lookup tables, etc.

Then when there is a schema change, you add the schema change to the script, and then you can regularly drop the development database and run the script to re-create a 'clean' copy. You can script out the objects by right-clicking on the database, select [Tasks...], and then [Generate Scripts...]. Follow the prompts. There are also third party tools for this purpose. Below are some resources that will help with scripting out data.

To change the Recovery Model, using Object Explorer, right-click on the database, select [Properties], then [Options]. You can change the Recovery model on that screen. Be sure to make a note to return the Recovery Model to [FULL] when you move the database to production. You may need to 'shrink' the log file since it has grown so large.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Database to File using SMO (VB.NET)
http://msdn2.microsoft.com/en-us/library/ms162138.aspx

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

|||Thanks for the info.

I set the Recovery Model to simple.

I deleted about 95% of the data.

I ran DBCC SHRINKFILE and shrunk the log file to 2 mb (FANTASTIC! - NO PROBLEMS)

But the file size for the DB still show 702 mb in Windows Explorer. So I looked at the Disk Usage where it shows 702 mb for SPACE RESERVED but only 4.3 mb for SPACE USED.

I wonder if this is because the field with the most data is ntext datatype (which I believe reserves a certain amount of disk space regardless of what is used. The db was created several years ago in Access and migrated to SQL Server 2000. I have considered changing the datatype to nvarchar (MAX) which I believe only reserves what is actually needed. Also, when I create a full-text index, I get an alert that the ntext datatype may prevent some features of WITH CHANGE_TRACKING AUTO;

Do you think the ntext datatype is keeping the file size so large? If so, is it simply a matter of changing datatype on that column? Or could there be other factors keeping the db file size so large?|||

First up the warning about ntext and fulltext search. Full Text change tracking does not support updates made via writetext or updatetext which is probably why you are getting that error. As you are using SQL2005 (?) i'd suggest changing the column type to nvarchar(max). Text, Ntext and image have been superceded but are supported for backward compatability.

As for the size of the database. When you delete data, the size of the datafile will not shrink but the pages within that will be marked as free. Generally speaking its a good idea to have your datafile at a size bigger than the current database size as this allows for growth of data without the (expensive) operation of growing the datafile.

In saying this, you are able to shrink the data file in a similar way to the transaction log.

HTH!

|||Thanks Rich,

I shrunk the db as well and it really does make a big difference while I developing in VB. I'm learning LINQ so there is alot of trial and error, so this really saves me some time.

Now to the question of what to do with the database when I am ready for full size (when I get my VB code worked out) since there is clearly some redundant data that will need to be deleted. What do you mean by "its a good idea to have your datafile at a size bigger than the current databse size as this allows for growth of data without the expensive operation of growing the datafile"?

I didn't realize that you could directly determine the size of the data other than choosing column datatypes that limit or extend size (e.g. nvarchar(10)). This was one of the concerns about the ntext datatype (besides being outdated). I had read that it used up a certain amount of space regardless of the data whereas nvarchar(max) is flexible in allocating space depending upon the amount of data, hence a smaller file size customized to the requirements of the data.

For this particular database, the size should not increase significantly once it is finished since users will not be able to add or delete data. From time to time corrections (typos) may need to be made but should not add substantiallly to the size.

|||

Sorry if my wording was a bit confusing.

When you create a database you can specify the size of the data and log files which essentially allocates space for your data to be stored.

If you set your files too small and you have a lot of database write activity, the files will physically need to grow to store the data. The physical growth of a file is expensive in terms of resources and you want to minimise this, so set the files at a size that reflects how big you predict your data to grow.

The size of the your columns etc is how you control how much data is stored within the datafile and this is more likely to be a factor in performance rather than the physical file size.

HTH!

|||Thanks for the helpful explanation. I will keep that in mind as I alter this db and create new ones.

As a followup to the datatype question, I have been trying to change the datatype ntext on the SectionText column to nvarchar(max), but am having a problem. My sql is:

ALTER TABLE FullDocuments MODIFY SectionText nvarchar(Max) not null;

I am getting this error message:

Incorrect syntax near 'MODIFY'.

Any thought on what I am doing wrong and how to fix it?|||

Thats not the correct syntax for that command:

Try:

Code Snippet

ALTER TABLE FullDocuments
ALTER COLUMN SectionText NVARCHAR(Max) NOT NULL;

Good luck!|||

Let me expand upon Rich's explanation.

When a database runs out of space, and if 'AutoGrow' is set to TRUE, it will automatically acquire additional disk space from the OS. Consider the analogy of a notebook. When SQL Server acquires disk space from the OS, it is like the notebook gets paper, and then that paper has to be divided into pages, lines drawn on the pages, and they have to be numbered before they are ready to use. Once done, it is quick and efficient for SQL Server to use the pages. But when the pages fill up, and SQL Server has to acquire additional space from the OS, it is a 'costly' operation to have to wait until all of the new pages are ready to use before the operation that needed to store data can continue. So it is best to 'size' the database large enough to contain the expected data growth over a good period of time. Then it is a 'Best Practice' to have a scheduled task that will periodically assess the need for additional space, and acquire that space during 'non-peak' times. (AutoGrow could fire off at the worst moment for server load.)

Perhaps these resources will help:

FileSize -Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thanks again.

Deleting backup files older than 5 days old.

I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.

I currently have E:\MSSQL.1\MSSQL\Backup\databasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bakIt looks like 2005 will handle this after the 2/07 patch is applied but until then I would like to handle this with a file task if possible. Thanks.|||

The FOREACH Loop container can enumerator the files in a directory, and takes wildcard expressions for the files to loop through. You can set it store each individual filename in a variable as it goes through the loop, and include a file task inside the loop to delete each file.

|||Thanks, jwelsh. Worked like a dream. I just loop through and delete anything older than 5 days old.

Monday, March 19, 2012

deleting a sql backup file

My backups job are failing at the delete old backup step
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa|||Thank you so much Carlos. The "verify integrity of backup
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because
sql server was still
>verifying the backup file. When you use database
maintenance plans, one of
>the options is to verify the file after the backup is
taken. If this is not
>the case you're going to have to find a windows utility
to find which
>windows process has the file locked, i think i found one
of these utilities
>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> My backups job are failing at the delete old backup step
>> because of not being to delete a backup file. When I
try
>> to manually delete this file I get the sharing violation
>> error message - that someone or something process may be
>> using the file. I know for a fact that it is not
someone
>> and I need to delete this file. I take ownership of the
>> file with full control permissions but still can't
delete
>> this file. I hate to bounce a server just to be able to
>> delete a file. Does anyone know what I can do to delete
>> this file? Is there a way to have sql server or agent
>> take control of the files I want the jobs to delete? I
>> appreciate your help. Thanks
>> Lisa
>
>.
>|||That option is IMO not that useful. To check out what it does, read about
the VERIFYONLY option to the RESTORE in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> >--Original Message--
> >Lisa,
> >This happened to me once a while ago and it was because
> sql server was still
> >verifying the backup file. When you use database
> maintenance plans, one of
> >the options is to verify the file after the backup is
> taken. If this is not
> >the case you're going to have to find a windows utility
> to find which
> >windows process has the file locked, i think i found one
> of these utilities
> >at www.sysinternals.com a while ago, but i'm not sure.
> >
> >--
> >Carlos E. Rojas
> >SQL Server MVP
> >Co-Author SQL Server 2000 Programming by Example
> >
> >
> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> >> My backups job are failing at the delete old backup step
> >> because of not being to delete a backup file. When I
> try
> >> to manually delete this file I get the sharing violation
> >> error message - that someone or something process may be
> >> using the file. I know for a fact that it is not
> someone
> >> and I need to delete this file. I take ownership of the
> >> file with full control permissions but still can't
> delete
> >> this file. I hate to bounce a server just to be able to
> >> delete a file. Does anyone know what I can do to delete
> >> this file? Is there a way to have sql server or agent
> >> take control of the files I want the jobs to delete? I
> >> appreciate your help. Thanks
> >>
> >> Lisa
> >
> >
> >.
> >|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> > Thank you so much Carlos. The "verify integrity of backup
> > upon completion" was checked. If this is locking the
> > files and uncheck that box, isn't that a bad thing? What
> > is the impact on the integrity of my backups. I will
> > check the winternals website also. Thanks.
> >
> > Lisa.
> >
> >
> > >--Original Message--
> > >Lisa,
> > >This happened to me once a while ago and it was because
> > sql server was still
> > >verifying the backup file. When you use database
> > maintenance plans, one of
> > >the options is to verify the file after the backup is
> > taken. If this is not
> > >the case you're going to have to find a windows utility
> > to find which
> > >windows process has the file locked, i think i found one
> > of these utilities
> > >at www.sysinternals.com a while ago, but i'm not sure.
> > >
> > >--
> > >Carlos E. Rojas
> > >SQL Server MVP
> > >Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> > wrote in message
> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> > >> My backups job are failing at the delete old backup step
> > >> because of not being to delete a backup file. When I
> > try
> > >> to manually delete this file I get the sharing violation
> > >> error message - that someone or something process may be
> > >> using the file. I know for a fact that it is not
> > someone
> > >> and I need to delete this file. I take ownership of the
> > >> file with full control permissions but still can't
> > delete
> > >> this file. I hate to bounce a server just to be able to
> > >> delete a file. Does anyone know what I can do to delete
> > >> this file? Is there a way to have sql server or agent
> > >> take control of the files I want the jobs to delete? I
> > >> appreciate your help. Thanks
> > >>
> > >> Lisa
> > >
> > >
> > >.
> > >
>|||Thank you very much.
>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
>> That option is IMO not that useful. To check out what
it does, read about
>> the VERIFYONLY option to the RESTORE in Books Online.
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> > Thank you so much Carlos. The "verify integrity of
backup
>> > upon completion" was checked. If this is locking the
>> > files and uncheck that box, isn't that a bad thing?
What
>> > is the impact on the integrity of my backups. I will
>> > check the winternals website also. Thanks.
>> >
>> > Lisa.
>> >
>> >
>> > >--Original Message--
>> > >Lisa,
>> > >This happened to me once a while ago and it was
because
>> > sql server was still
>> > >verifying the backup file. When you use database
>> > maintenance plans, one of
>> > >the options is to verify the file after the backup is
>> > taken. If this is not
>> > >the case you're going to have to find a windows
utility
>> > to find which
>> > >windows process has the file locked, i think i found
one
>> > of these utilities
>> > >at www.sysinternals.com a while ago, but i'm not
sure.
>> > >
>> > >--
>> > >Carlos E. Rojas
>> > >SQL Server MVP
>> > >Co-Author SQL Server 2000 Programming by Example
>> > >
>> > >
>> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> > wrote in message
>> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> > >> My backups job are failing at the delete old
backup step
>> > >> because of not being to delete a backup file.
When I
>> > try
>> > >> to manually delete this file I get the sharing
violation
>> > >> error message - that someone or something process
may be
>> > >> using the file. I know for a fact that it is not
>> > someone
>> > >> and I need to delete this file. I take ownership
of the
>> > >> file with full control permissions but still can't
>> > delete
>> > >> this file. I hate to bounce a server just to be
able to
>> > >> delete a file. Does anyone know what I can do to
delete
>> > >> this file? Is there a way to have sql server or
agent
>> > >> take control of the files I want the jobs to
delete? I
>> > >> appreciate your help. Thanks
>> > >>
>> > >> Lisa
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>Lis|||Thanks Tibor
>--Original Message--
>That option is IMO not that useful. To check out what it
does, read about
>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> Thank you so much Carlos. The "verify integrity of
backup
>> upon completion" was checked. If this is locking the
>> files and uncheck that box, isn't that a bad thing?
What
>> is the impact on the integrity of my backups. I will
>> check the winternals website also. Thanks.
>> Lisa.
>>
>> >--Original Message--
>> >Lisa,
>> >This happened to me once a while ago and it was because
>> sql server was still
>> >verifying the backup file. When you use database
>> maintenance plans, one of
>> >the options is to verify the file after the backup is
>> taken. If this is not
>> >the case you're going to have to find a windows utility
>> to find which
>> >windows process has the file locked, i think i found
one
>> of these utilities
>> >at www.sysinternals.com a while ago, but i'm not sure.
>> >
>> >--
>> >Carlos E. Rojas
>> >SQL Server MVP
>> >Co-Author SQL Server 2000 Programming by Example
>> >
>> >
>> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> >> My backups job are failing at the delete old backup
step
>> >> because of not being to delete a backup file. When I
>> try
>> >> to manually delete this file I get the sharing
violation
>> >> error message - that someone or something process
may be
>> >> using the file. I know for a fact that it is not
>> someone
>> >> and I need to delete this file. I take ownership of
the
>> >> file with full control permissions but still can't
>> delete
>> >> this file. I hate to bounce a server just to be
able to
>> >> delete a file. Does anyone know what I can do to
delete
>> >> this file? Is there a way to have sql server or
agent
>> >> take control of the files I want the jobs to
delete? I
>> >> appreciate your help. Thanks
>> >>
>> >> Lisa
>> >
>> >
>> >.
>> >
>
>.
>

deleting a sql backup file

quote:

> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa

|||Thank you so much Carlos. The "verify integrity of backup
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.

quote:

>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because

sql server was still

quote:

>verifying the backup file. When you use database

maintenance plans, one of

quote:

>the options is to verify the file after the backup is

taken. If this is not

quote:

>the case you're going to have to find a windows utility

to find which

quote:

>windows process has the file locked, i think i found one

of these utilities

quote:

>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>

wrote in message

quote:

>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
try[QUOTE]
someone[QUOTE]
delete[QUOTE]
>
>.
>

|||That option is IMO not that useful. To check out what it does, read about
the VERIFYONLY option to the RESTORE in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...[QUOTE]
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> sql server was still
> maintenance plans, one of
> taken. If this is not
> to find which
> of these utilities
> wrote in message
> try
> someone
> delete|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>

|||Thank you very much.

quote:

>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 programming by Example
>
>"Tibor Karaszi"

<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in

quote:

>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
it does, read about[QUOTE]
>http://groups.google.com/groups?

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

wrote in message[QUOTE]
backup[QUOTE]
What[QUOTE]
because[QUOTE]
utility[QUOTE]
one[QUOTE]
sure.[QUOTE]
backup step[QUOTE]
When I[QUOTE]
violation[QUOTE]
may be[QUOTE]
of the[QUOTE]
able to[QUOTE]
delete[QUOTE]
agent[QUOTE]
delete? I[QUOTE]
>
>.
>Lis

|||Thanks Tibor

quote:

>--Original Message--
>That option is IMO not that useful. To check out what it

does, read about

quote:

>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>

wrote in message

quote:

>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
backup[QUOTE]
What[QUOTE]
one[QUOTE]
step[QUOTE]
violation[QUOTE]
may be[QUOTE]
the[QUOTE]
able to[QUOTE]
delete[QUOTE]
agent[QUOTE]
delete? I[QUOTE]
>
>.
>

Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someone
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.
Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think someone
> changed it to full, and the Trans logs never got backed up. In any case, is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something; we
> do full backups of the dbs daily. Anyway, if anyone can let me know if what
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||So, the answer in whether I can just delete the log file and recreate it then
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it then
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi,
you cant delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why thi
s
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someon
e
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why t
his
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think some
one
> changed it to full, and the Trans logs never got backed up. In any case,
is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something;
we
> do full backups of the dbs daily. Anyway, if anyone can let me know if wh
at
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn
't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/micr...3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, the answer in whether I can just delete the log file and recreate it the
n
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/micr...3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it t
hen
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
you can=B4t delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Thursday, March 29, 2012

Deleting records in the logfile

Tuesday, March 27, 2012

Deleting or Clearing

Deleting Old .BAK file Problem

Deleting Old .BAK file Problem

Sunday, March 25, 2012

Deleting log file in sql 2000 database

Deleting log file in sql 2000 database

Deleting Log file

Deleting Log file

Deleting Log file

Deleting lines from a text file

Deleting Files using SSIS Scripting Object

Deleting FileGroup/(.ldf) file

Thursday, March 22, 2012

Deleting extra tempdb log and data files

Deleting Existing Data before Loading New Data

Wednesday, March 21, 2012

Deleting data bloats log file

Deleting backup files older than 5 days old.

Monday, March 19, 2012

deleting a sql backup file

deleting a sql backup file

Deleting a Log file and recreating it...

Deleting a Log file and recreating it...

DENY permission,SQL server

Blog Archive

About Me