DENY permission,SQL server: files

Showing posts with label files. Show all posts

Tuesday, March 27, 2012

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Deleting older backup files - Not Happening

I have a job that backs up all my databases. The problems
is that I have it set to delete .BAK files older than 3
days, but it is not. Does anyone know why this could be?
If you have any suggestions please let me know.
Thanks.Make sure the SQL Agent login has the correct permissions on the files.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> I have a job that backs up all my databases. The problems
> is that I have it set to delete .BAK files older than 3
> days, but it is not. Does anyone know why this could be?
> If you have any suggestions please let me know.
> Thanks.|||By the time the delete starts, it has not been 3 days
yet ?. If this is scheduled through the maintenance plan
and your database backup is taking shorter time than
before, it will not delete the old backup files. You may
want to delete the files manually or setup a batch job (or
separate cmd job) to delete it......
>--Original Message--
>I have a job that backs up all my databases. The
problems
>is that I have it set to delete .BAK files older than 3
>days, but it is not. Does anyone know why this could
be?
>If you have any suggestions please let me know.
>Thanks.
>.
>|||Shawn
Looks like you are using database maintenance plan.
Check if you set 'attempt to repaire any minor problems' ,sometime it
causes to SQL Server to be set with single user mode.
"Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> I have a job that backs up all my databases. The problems
> is that I have it set to delete .BAK files older than 3
> days, but it is not. Does anyone know why this could be?
> If you have any suggestions please let me know.
> Thanks.|||This is very helpful article
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans
.asp
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#GjdvLuzDHA.2412@.TK2MSFTNGP10.phx.gbl...
> Shawn
> Looks like you are using database maintenance plan.
> Check if you set 'attempt to repaire any minor problems' ,sometime it
> causes to SQL Server to be set with single user mode.
>
>
> "Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
> news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> > I have a job that backs up all my databases. The problems
> > is that I have it set to delete .BAK files older than 3
> > days, but it is not. Does anyone know why this could be?
> > If you have any suggestions please let me know.
> >
> > Thanks.
>

deleting old backups/trn files.

i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.

Anyone have ny problems with maintenance plans not cleaning up when told?

ano because I do not use them. I create my own jobs.|||Maintenance plans are nothing but wizards that create SQL Agent jobs. If the job is modified, the maintenance plan has no clue that anything has changed, and if the maintenance plan is re-edited it will overwrite any other changes made to the jobs.
Maintenance plans are top candidates for the most confusing and misleading functionality within SQL server. I avoid them, except as a means of defining groups of databases for administrative purposes.
Open up the job in SQL Agent and check the code that is being run. It should be something like "EXEC xp_sqlmaint '-PlanID 02A52657-D546-11D1-9D8A-00A0C9054212...".
Post it here.|||here you go...

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 2A998DBD-3F5D-4685-ACCA-70354638C8C5 -Rpt "D:\mssql\REPORTS\live DB Maintenance4.txt" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB "D:\mssql\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'|||There is an additional parameter for specifying deletion of reports, and for some reason this parameter is left out of the sql_maint documentation in Books Online. I am thinking it is "-DelRpts 4WEEKS", but I am not sure. I will look it up for you once I get back to my office.

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
AntOn Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html

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 files

Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need any
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
AntHi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
--
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> Hi,
>> We have many databases in production on our server. Each are generating
>> log
>> files which have grown very large. I'd like to delete them & basically
>> start
>> 'growing them' again.
>> Basically, I want to stop tracing, delete the log files then start
>> tracing
>> again in order to generate new ones. We have nightly backups & don't need
>> any
>> point of time restores prior to today.
>> What are the dangers in this. Should I simply go ahead & do this?
>> Thanks for any advice on this
>> Ant
>|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> > Hi,
> >
> > We have many databases in production on our server. Each are generating
> > log
> > files which have grown very large. I'd like to delete them & basically
> > start
> > 'growing them' again.
> >
> > Basically, I want to stop tracing, delete the log files then start tracing
> > again in order to generate new ones. We have nightly backups & don't need
> > any
> > point of time restores prior to today.
> >
> > What are the dangers in this. Should I simply go ahead & do this?
> >
> > Thanks for any advice on this
> > Ant
>|||Don't just delete the ldf files. They are an integral part of the database. It's like saying that
you need more space on the hd and want to remove the windows folder. Shrink the file (if really
needed), then do a manual grow to the necessary size. And then either put the database in simple
recovery mode, or do regular transaction log backups. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start tracing
>> > again in order to generate new ones. We have nightly backups & don't need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||> We backup every evening.
What types of backups?. Note that a database backup does not remove
uncommitted transactions from the log so the log files will grow
indefinitely. The only way to remove committed log data is with a LOG
backup or by keeping the database in the SIMPLE recovery model. In the FULL
or BULK_LOGGED model, one normally schedules periodic LOG backups in between
to full backups.
Once you've backed up your logs, you can shrink the log file back to a
reasonable size as Tibor suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||Hello,
If your recovery model is FULL or BULK_LOGGED you need to perform
Transaction Log Backup in frequent intervals. This will make sure that LDF
file will not grow.
Follw the below steps:-
1. Schedule a Daily full backup once a day.
2. Schedule a Transaction Log backup every 30 minutes
3. After the completion of daily full database backup you could delete all
the previous transction log backup files
This approach will help you to recover the database fully.
If your current LDF file is huge then use DBCC SHRINKFILE to reduce the
size. Before shrink either truncate the Log or Backup the log.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>

Deleting filesystem files

Hi All,
I need a generic solution to delete backup files from filesystem in order to
reduce space.
Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
show me an example on how to achieve this.
ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delete
all files in a specific folder which are older then "N" number of hours.
Its really very urgent. Can anyone give me a quick solution / sample.
Regards
PrasanthIF you are using a Maintenance plan for creating the backups, then you have
an option when you are setting up the plan through the wizard where you can
use the option
"Remove files older than"
If you cannot do that, you can use a vbscript to automate the delete.
On how to do that.. check out this link..
[url]http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp[/u
rl]
Hope this helps.|||As omnibuzz suggested vbscript is the way to go.
If at you are not interest in that for some reason! then try these
suggestions:
1. How about writing a batch script to do this job. THen you can call this
bat file within a sqljob and schedule it.
2. Write a windows application to do and then schedule it using Windows
Scheduler.
3. Write a Windows service to do this job.
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:

> Hi All,
> I need a generic solution to delete backup files from filesystem in order
to
> reduce space.
> Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
> show me an example on how to achieve this.
> ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delet
e
> all files in a specific folder which are older then "N" number of hours.
> Its really very urgent. Can anyone give me a quick solution / sample.
> Regards
> Prasanth

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 files

Hello there
On every w

by Enterprise manager i:
1. Detatch the database
2. delete the log file
3. reattaching it again
I would like to do this by code.
Is there a way to do that?Roy Goldhammer wrote:

> Hello there
> On every w

by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
Yes. Don't. Regularly detaching and deleting the log in an operational
system is certainly foolish, unnecessary and/or dangerous.
Firstly, set the correct recovery model - that means "Simple Recovery"
if you don't require log backups. Secondly, ensure you implement the
right tran log backups if you do need them. Finally, set the log file
to the right size, turn off autogrow and LEAVE IT ALONE.
More info:
http://support.microsoft.com/?id=110139
http://support.microsoft.com/?id=317375
http://support.microsoft.com/?id=315512
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
David Portas
SQL Server MVP
--|||Hi
Why?
What you are doing is unsupported and very dangerous. In some cases, you
will not be able to re-attach the database again and kiss your DB goodbye.
Rather put your database into the appropriate recovery mode, and backup the
log if it is not in simple mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w

by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>|||I agree with David and Mike. Don't do this. It will bite you in the
backside.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w

by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>

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

Wednesday, March 21, 2012

Deleting binn files

I'm running a copy of SQL-Server v7 on a win2k machine,
and I've run into the problem of files randomly
disappearing from the \80\tools\binn directory. So far,
over 40 files have gone missing from the binn dir,
including all the files and dlls to run dtsrun and the
query analyzer. Has anyone ever seen this kind of
behaviour on their sql installation, or know what causes
it?
btw, I'm running sp3 on the system.
-IanOops, I accidentally made a mistake with my post... the
version of SQL that I'm running is actually SQL 2000...
does that affect the nature of the problem?
I appreciate your help w/ this problem.
>--Original Message--
>\80\tools\binn directory is for SQL 2000 not for SQL V7.
>It is very unlikely to be deleted these files randomly.
>Because, if you are running these application and if you
>try to delete these dlls, you get access violation.
>If you are running multiple instances for example SQL 7
as
>default, SQL 2000 as second instance, then there is a
>possibility of some files taken out from one of these
>directories(particularly common resources for both
>instances). This happens only when the installation
>process for 2 instances are not done properly.
>Chinna.
>>--Original Message--
>>I'm running a copy of SQL-Server v7 on a win2k machine,
>>and I've run into the problem of files randomly
>>disappearing from the \80\tools\binn directory. So far,
>>over 40 files have gone missing from the binn dir,
>>including all the files and dlls to run dtsrun and the
>>query analyzer. Has anyone ever seen this kind of
>>behaviour on their sql installation, or know what causes
>>it?
>>btw, I'm running sp3 on the system.
>>-Ian
>>.
>.
>|||I have more info on the actual files that were deleted...
I compared the contents of the binn directory to another
machine running a similar installation of SQL 2000, and
these are the files that appear to be missing:
colctrl.dll
dmcomscm.exe
dtsrun.exe
dtsruni.exe
dtswiz.exe
hhsetup.dll
isql.exe
itwiz.exe
odbccmpt.exe
psdcscm.dll
replmon.pmc
sqlbrows.cnt
sqlbrows.hlp
sqldmo80.cnt
sqldmo80.hlp
sqldts80.cnt
sqldts80.hlp
sqlftwiz.exe
sqllex.dll
sqlmmmem.chm
sqlmmcii.chm
sqlns80.cnt
sqlns80.hlp
uncol.exe
wiztrace.exe
Once again, thanx for your help on this...
-Ian
>--Original Message--
>I'm running a copy of SQL-Server v7 on a win2k machine,
>and I've run into the problem of files randomly
>disappearing from the \80\tools\binn directory. So far,
>over 40 files have gone missing from the binn dir,
>including all the files and dlls to run dtsrun and the
>query analyzer. Has anyone ever seen this kind of
>behaviour on their sql installation, or know what causes
>it?
>btw, I'm running sp3 on the system.
>-Ian
>.
>|||\80\tools\binn directory is for SQL 2000 not for SQL V7.
It is very unlikely to be deleted these files randomly.
Because, if you are running these application and if you
try to delete these dlls, you get access violation.
If you are running multiple instances for example SQL 7 as
default, SQL 2000 as second instance, then there is a
possibility of some files taken out from one of these
directories(particularly common resources for both
instances). This happens only when the installation
process for 2 instances are not done properly.
Chinna.
>--Original Message--
>I'm running a copy of SQL-Server v7 on a win2k machine,
>and I've run into the problem of files randomly
>disappearing from the \80\tools\binn directory. So far,
>over 40 files have gone missing from the binn dir,
>including all the files and dlls to run dtsrun and the
>query analyzer. Has anyone ever seen this kind of
>behaviour on their sql installation, or know what causes
>it?
>btw, I'm running sp3 on the system.
>-Ian
>.
>

Deleting BAK and TRN files

I have a mentanence plan set to backup .trn and .bak files daily and then
delete the ones over 3 days old.
The problem is that they don't delete after 3 days....they just build up
and up!!!
Is this a known issue? or am i doing something wrong?
Any response is much appreciated!!!
Cheers,
Alex
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"alexbax" <alexbax@.discussions.microsoft.com> wrote in message
news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>I have a mentanence plan set to backup .trn and .bak files daily and then
> delete the ones over 3 days old.
> The problem is that they don't delete after 3 days....they just build up
> and up!!!
> Is this a known issue? or am i doing something wrong?
> Any response is much appreciated!!!
> Cheers,
> Alex
>
|||Thats great! ive made a few changes and will check to see if it works in a
few days!!
Thanks,
Alex
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "alexbax" <alexbax@.discussions.microsoft.com> wrote in message
> news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>
>
sql

Deleting BAK and TRN files

I have a mentanence plan set to backup .trn and .bak files daily and then
delete the ones over 3 days old.
The problem is that they don't delete after 3 days....they just build up
and up!!!
Is this a known issue? or am i doing something wrong'
Any response is much appreciated!!!
Cheers,
AlexBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"alexbax" <alexbax@.discussions.microsoft.com> wrote in message
news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>I have a mentanence plan set to backup .trn and .bak files daily and then
> delete the ones over 3 days old.
> The problem is that they don't delete after 3 days....they just build up
> and up!!!
> Is this a known issue? or am i doing something wrong'
> Any response is much appreciated!!!
> Cheers,
> Alex
>|||Thats great! ive made a few changes and will check to see if it works in a
few days!!
Thanks,
Alex
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "alexbax" <alexbax@.discussions.microsoft.com> wrote in message
> news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
> >I have a mentanence plan set to backup .trn and .bak files daily and then
> > delete the ones over 3 days old.
> > The problem is that they don't delete after 3 days....they just build up
> > and up!!!
> >
> > Is this a known issue? or am i doing something wrong'
> >
> > Any response is much appreciated!!!
> >
> > Cheers,
> >
> > Alex
> >
> >
>
>

Deleting BAK and TRN files

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 additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would write
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
kdirks
Rainbow Energy Marketing
If your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')
|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Deleting additional data files in a database primary filegroup

I created two additional data files in our database so that when the primary
location was full it would write to the added files. I though it would writ
e
to the 2nd file and fill it before writing to the 3rd. It is writing to
both. Is it writing redundant data, or different data for better efficiency
?
Also, how can I delete the 3rd data file without losing information? The
3rd location is not a RAID device, so I would like to keep as much
information in the data file in the 2nd location as possible. Any ideas or
suggestions?
--
kdirks
Rainbow Energy MarketingIf your files are part of the PRIMARY filegroup, SQL Server is free to
place data in either one. It doesn't write the same data to both.
Moving the second file to a RAID device makes good sense. See the
ALTER DATABASE topic in books online for a complete explanation
(including removing files, if you're worried about the extra). Here's
a quick example I used to move the tempDB data files off my primary
data array...
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql\MSSQL$SQL2K\data\tempdb_data.mdf')|||Oh, yes, I believe you will need to restart your sql server for the
actual file move to take place.

Tuesday, March 27, 2012

Deleting Older Files

Deleting older backup files - Not Happening

deleting old backups/trn files.

Deleting old backups

Deleting Old .BAK file Problem

Deleting Old .BAK file Problem

Sunday, March 25, 2012

Deleting log files

Deleting log files

Deleting log files

Deleting filesystem files

Deleting Files using SSIS Scripting Object

Deleting files

Thursday, March 22, 2012

Deleting extra tempdb log and data files

Wednesday, March 21, 2012

Deleting binn files

Deleting BAK and TRN files

Deleting BAK and TRN files

Deleting BAK and TRN files

Deleting backup files older than 5 days old.

Monday, March 19, 2012

Deleting additional data files in a database primary filegroup

Deleting additional data files in a database primary filegroup

DENY permission,SQL server

Blog Archive

About Me