Filling transaction log

Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a day.
i don't know where the problem exists,
please help me
They should define "auto truncate". Auto shrink is different from backing
up the log or having the recovery model set to simple.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a
day.
i don't know where the problem exists,
please help me
|||What database options are turned on? How much data is actually in the
database?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
> Hi Guys,
> I've a problem regarding transaction log, i have a databasse
> which is to be accessed very rairaly inspite of that after few days it
> shows
> warning as:
> APPROACHING RESOURCE LIMIT
> MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
> while i don't have control on my DB some other company
> takes
> care of my DB operation like truncating transaction log etc. when i talked
> with them they said that transaction log auto truncates several time in a
> day.
> i don't know where the problem exists,
> please help me
>
sql

Files

Hello Professionals.
We have implemented the RAID 5 so what would be best performance tips for
separating the log and datafiles ? and any other tips for performance would
be highly appreciated.
Thanks
Hi,
How many Disk controllers you have in your server?. If you have multiple
controllers then you can keep MDF and LDF files
seperately in each controller. based on the access you can also create
seperate filegroups to keep the indexes. This will share the I/O
between controllers.
See the below site for Hardware tuning tips (See I/O part):-
http://www.sql-server-performance.co...are_tuning.asp
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:D477C1B8-8B65-4184-897B-D16B338A0EA7@.microsoft.com...
> Hello Professionals.
> We have implemented the RAID 5 so what would be best performance tips for
> separating the log and datafiles ? and any other tips for performance
> would
> be highly appreciated.
> Thanks
sql

FileIOPermission

I got my custom assembly working fine until I add writing to a log
file. Then I get a FileIOPermission error.
I have the following code in my policy file:
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="ReportHelperFilePermissionSet"
Description="A special permission set that grants read access to my
currency rates file.">
<IPermission
class="FileIOPermission"
version="1"
All="C:\ReportHelper.log"/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"/>
</PermissionSet>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="ReportHelperFilePermissionSet"
Name="MyNewCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportHelper.dll"/>
</CodeGroup>
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="AWCLibrary">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportHelper.dll"/>
</CodeGroup>
I write to my log file as follows:
public static void WriteLogFile(String msg)
{
FileIOPermission perm1 = new
FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
perm1.Assert();
//try
//{
FileStream fs = new FileStream(@."C:\ReportHelper.log",
FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter w = new StreamWriter(fs);
w.BaseStream.Seek(0, SeekOrigin.End);
w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
DateTime.Now.ToLongDateString());
w.Write(msg + "\r\n");
w.Flush();
w.Close();
}
What am I missing here? Any help would be appreciated.
Thanks!Check this
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
HTH
If still you face the issue, let me know (bkkrishnan [at] hotmail [dot] com)
Thanks
Balaji
Siwy wrote:
>I got my custom assembly working fine until I add writing to a log
>file. Then I get a FileIOPermission error.
>I have the following code in my policy file:
><PermissionSet
> class="NamedPermissionSet"
> version="1"
> Name="ReportHelperFilePermissionSet"
> Description="A special permission set that grants read access to my
>currency rates file.">
> <IPermission
> class="FileIOPermission"
> version="1"
> All="C:\ReportHelper.log"/>
> <IPermission
> class="SecurityPermission"
> version="1"
> Flags="Execution, Assertion"/>
></PermissionSet>
><CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="ReportHelperFilePermissionSet"
> Name="MyNewCodeGroup"
> Description="A special code group for my custom assembly.">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportHelper.dll"/>
></CodeGroup>
><CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="AWCLibrary">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportHelper.dll"/>
></CodeGroup>
>I write to my log file as follows:
>public static void WriteLogFile(String msg)
> {
> FileIOPermission perm1 = new
>FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
> perm1.Assert();
> //try
> //{
> FileStream fs = new FileStream(@."C:\ReportHelper.log",
>FileMode.OpenOrCreate, FileAccess.ReadWrite);
> StreamWriter w = new StreamWriter(fs);
> w.BaseStream.Seek(0, SeekOrigin.End);
> w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> DateTime.Now.ToLongDateString());
> w.Write(msg + "\r\n");
> w.Flush();
> w.Close();
> }
>What am I missing here? Any help would be appreciated.
>Thanks!
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1|||Hi Balaji,
I followed the article and it still doesn't work. I added [assembly:
AllowPartiallyTrustedCallers] to my assembly and removed extra
PermissionSet and CodeGroup sections from my policy file (I left the
"FullTrust" one) and I still get FileIOPermission error.
Thanks,
Kris
BALAJI via SQLMonster.com wrote:
> Check this
> http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
> HTH
> If still you face the issue, let me know (bkkrishnan [at] hotmail [dot] com)
> Thanks
> Balaji
>
> Siwy wrote:
> >I got my custom assembly working fine until I add writing to a log
> >file. Then I get a FileIOPermission error.
> >
> >I have the following code in my policy file:
> >
> ><PermissionSet
> > class="NamedPermissionSet"
> > version="1"
> > Name="ReportHelperFilePermissionSet"
> > Description="A special permission set that grants read access to my
> >currency rates file.">
> > <IPermission
> > class="FileIOPermission"
> > version="1"
> > All="C:\ReportHelper.log"/>
> > <IPermission
> > class="SecurityPermission"
> > version="1"
> > Flags="Execution, Assertion"/>
> ></PermissionSet>
> >
> ><CodeGroup class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="ReportHelperFilePermissionSet"
> > Name="MyNewCodeGroup"
> > Description="A special code group for my custom assembly.">
> > <IMembershipCondition
> > class="UrlMembershipCondition"
> > version="1"
> > Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportHelper.dll"/>
> ></CodeGroup>
> ><CodeGroup
> > class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="FullTrust"
> > Name="AWCLibrary">
> > <IMembershipCondition
> > class="UrlMembershipCondition"
> > version="1"
> > Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportHelper.dll"/>
> ></CodeGroup>
> >
> >I write to my log file as follows:
> >
> >public static void WriteLogFile(String msg)
> > {
> > FileIOPermission perm1 = new
> >FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
> > perm1.Assert();
> > //try
> > //{
> > FileStream fs = new FileStream(@."C:\ReportHelper.log",
> >FileMode.OpenOrCreate, FileAccess.ReadWrite);
> > StreamWriter w = new StreamWriter(fs);
> > w.BaseStream.Seek(0, SeekOrigin.End);
> > w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> > DateTime.Now.ToLongDateString());
> > w.Write(msg + "\r\n");
> > w.Flush();
> >
> > w.Close();
> > }
> >
> >What am I missing here? Any help would be appreciated.
> >
> >Thanks!
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1

File timestamps not advancing

Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?
> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!
|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.
|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?
|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...

File timestamps not advancing

Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...

File timestamps not advancing

Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

file size of restore

I don't know why it wants 500GB but you do need the entire size of the data
and log files not just the data itself. It is a good idea to have extra
space but you may be pushing it a little too far if you only have 3GB.
Andrew J. Kelly SQL MVP
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:VvGdna6Hf7i_DF3bnZ2dnUVZ8rOdnZ2d@.bt.com...
> Hi
> I did an initial file size of a db to 100GB .
> The backups reflect the true size of the DB (about 3GB). However if you
> try and restore the DB it wants 500GB of disk space. Is there anyway to
> restore so it only restores the size of the data?
>
>
>
>
>
>I don't know why it wants 500GB
Perhaps the initial size was 100GB and it has now grown to 500GB? Doesn't change what you are
saying, though... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMhxzCd3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>I don't know why it wants 500GB but you do need the entire size of the data and log files not just
>the data itself. It is a good idea to have extra space but you may be pushing it a little too far
>if you only have 3GB.
> --
> Andrew J. Kelly SQL MVP
> "Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
> news:VvGdna6Hf7i_DF3bnZ2dnUVZ8rOdnZ2d@.bt.com...
>

File Size for SQL Server 2005 Error Log

Hello,
My SQL Server 2005 error log is getting very, very large... Like 4 GB in 4
days... How can I recycle my error log so that it doesnt' overrun my c:
drive. Also, if there's a way to move the error log from the c: drive to
another drive, that would also help.
Thanks.
Adam F. HarrisOn Mar 14, 5:39 pm, "Adam Harris" <a...@.jgo.com> wrote:
> Hello,
> My SQL Server 2005 error log is getting very, very large... Like 4 GB in 4
> days... How can I recycle my error log so that it doesnt' overrun my c:
> drive. Also, if there's a way to move the error log from the c: drive to
> another drive, that would also help.
> Thanks.
> Adam F. Harris
You will want to execute sp_cycle_errorlog periodically. I would
suggest scheduling a job that runs it as well.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hello Enrique,
Ok, that sounds great, but I have no idea how to do that... Do you have a
website or documentation to do that?
Thanks.
Adam F. Harris
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173921963.231488.253620@.b75g2000hsg.googlegroups.com...
> On Mar 14, 5:39 pm, "Adam Harris" <a...@.jgo.com> wrote:
>> Hello,
>> My SQL Server 2005 error log is getting very, very large... Like 4 GB in
>> 4
>> days... How can I recycle my error log so that it doesnt' overrun my c:
>> drive. Also, if there's a way to move the error log from the c: drive to
>> another drive, that would also help.
>> Thanks.
>> Adam F. Harris
> You will want to execute sp_cycle_errorlog periodically. I would
> suggest scheduling a job that runs it as well.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

File placement for optimal performance?

What is the best performance for this configuration:

Files:

Data

Log

Indexes

tempdb

Disk:

A - RAID 10

B - RAID 10 (or should this be RAID 1?)

Whats best?:

A - Data and Indexes

B - tempdb and Log

? Thanks.

check these links

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

http://sql-server-performance.com/Community/forums/t/2337.aspx

Madhu

File of LOG

Somebody knows as removing the log file, because the same this occupying a
space.Frank,
Are you talking about transaction log, .ldf file?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:O2ThWl$aDHA.2476@.tk2msftngp13.phx.gbl...
>
>
> Somebody knows as removing the log file, because the same this occupying a
> space.
>|||Yes, because the same already this with 700M
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:um3qcr$aDHA.2436@.TK2MSFTNGP12.phx.gbl...
> Frank,
> Are you talking about transaction log, .ldf file?
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> news:O2ThWl$aDHA.2476@.tk2msftngp13.phx.gbl...
> >
> >
> >
> >
> >
> > Somebody knows as removing the log file, because the same this occupying
a
> > space.
> >
> >
>|||Frank,
You shouldnt remove the transaction log file.Instead take measures to
control its growth. There is no automatic shrinking inbuilt unless you
specify.If you care about log backups, then schedule a sql job which will
take log backups at regular intervals.If you dont care, then check the
option "truncate log on checkpoint" on database properties(sql7), "simple"
recovery model(sql2000) and BACKUP LOG <databasename> WITH TRUNCATE_ONLY
.These actions would only truncate ( or empty ) the log so that no further
space is extracted unless needed.In order to shrink (or reduce the physical
size of the file ), refer:
--SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/support/kb/Articles/q256/6/50.asp
and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
--SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:%23mg$wQKbDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Yes, because the same already this with 700M
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:um3qcr$aDHA.2436@.TK2MSFTNGP12.phx.gbl...
> > Frank,
> >
> > Are you talking about transaction log, .ldf file?
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> > news:O2ThWl$aDHA.2476@.tk2msftngp13.phx.gbl...
> > >
> > >
> > >
> > >
> > >
> > > Somebody knows as removing the log file, because the same this
occupying
> a
> > > space.
> > >
> > >
> >
> >
>

File locations

Is it possible to reassign the data and transaction log file locations after
creating a DB? If so how? Thanks to everyone for being there to help. SQL
server 2000
Simplest way would be to detach the database, move it to a new location an
then attach it again (not recommended for a production database).
Cristian Lefter, SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000
|||Cristian's advice is sound but you may find these helpful:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000
|||Hi,
1. Use the procedure SP_DETACH_DB <dbname> to detach the database
2. Create a new folder in new drive and copy the MDF and LDF to new folder
3. Use sp_attach_db
'dbname','driveletter\folder\dXbname.mdf','drivele tter\folderX\dbname.ldf'
FYI, This approach need some down time.
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL

> server 2000

File locations

Is it possible to reassign the data and transaction log file locations after
creating a DB? If so how? Thanks to everyone for being there to help. SQL
server 2000Simplest way would be to detach the database, move it to a new location an
then attach it again (not recommended for a production database).
Cristian Lefter, SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000|||Cristian's advice is sound but you may find these helpful:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000|||Hi,
1. Use the procedure SP_DETACH_DB <dbname> to detach the database
2. Create a new folder in new drive and copy the MDF and LDF to new folder
3. Use sp_attach_db
'dbname','driveletter\folder\d_bname.mdf','driveletter\folder_\dbname.ldf'
FYI, This approach need some down time.
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL

> server 2000

File locations

Is it possible to reassign the data and transaction log file locations after
creating a DB? If so how? Thanks to everyone for being there to help. SQL
server 2000Simplest way would be to detach the database, move it to a new location an
then attach it again (not recommended for a production database).
Cristian Lefter, SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000|||Cristian's advice is sound but you may find these helpful:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
> after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000|||Hi,
1. Use the procedure SP_DETACH_DB <dbname> to detach the database
2. Create a new folder in new drive and copy the MDF and LDF to new folder
3. Use sp_attach_db
'dbname','driveletter\folder\d­bname.mdf','driveletter\folder­\dbname.ldf'
FYI, This approach need some down time.
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:D2874151-8BF6-424F-A1A9-A17C2F175374@.microsoft.com...
> Is it possible to reassign the data and transaction log file locations
after
> creating a DB? If so how? Thanks to everyone for being there to help. SQL
> server 2000

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?

File growth problem in 2005

Have a database, one filegroup with one datafile and one log file.

However, restarted the box and saw that the growth settings for the datafile was changed from 100Mb increments to 25600%. The logfile is still the same at 200Mb as required. Both have unlimited growth.

Looking at sysfiles in the database, growth is 25600 for the log and data file. In Management Studio I cannot change the setting for the data file (log file still shows 200Mb growth increments), Studio tells me "Value 25600 is not valid for Value. Value should be between Minimum and Maximum.

Any thoughts? Cant really have a database sitting with this growth rate, and cannot change the value in sysfiles either (even with sp_configure etc).

Any ideas? Thanks

Have you tried clicking on the button next to the text that describes the growth. This provides the means to change the growth settings. The button has ellipse on it ...|||Seems to be a GUI problem. Try changing the autogrow value using ALTER DATABASE instead.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:19115900-bfb9-4dff-8042-9b7462a44781@.discussions.microsoft.com...

> Have a database, one filegroup with one datafile and one log file.

>

> However, restarted the box and saw that the growth settings for the

> datafile was changed from 100Mb increments to 25600%. The logfile is

> still the same at 200Mb as required. Both have unlimited growth.

>

> Looking at sysfiles in the database, growth is 25600 for the log and

> data file. In Management Studio I cannot change the setting for the data

> file (log file still shows 200Mb growth increments), Studio tells me

> "Value 25600 is not valid for Value. Value should be between Minimum and

> Maximum.

>

> Any thoughts? Cant really have a database sitting with this growth rate,

> and cannot change the value in sysfiles either (even with sp_configure

> etc).

>

> Any ideas? Thanks

>

>|||

yeah, it's a GUI problem preventing me to change that value (I got that error from clicking the ... button to change it).

My other worry is what changed this, the system was set to grow in increments of 100Mb, settings omething to 25600% increase (when you cant even get to that value in the GUI) means something on the server changed ther settings.

|||This seems to be a bug on the SQL engine. I've had it happen twice now. First time I assumed I had done something wrong, reset the autogrow values to a number. This morning it has happened again and the server has no disk space left. 100% sql engine bug I think.|||

Further to this see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

The only workaround I have so far is to set Max size of the file to prevent the file from growing too large, or to turn off AutoGrow

File growth problem in 2005

Have a database, one filegroup with one datafile and one log file.

However, restarted the box and saw that the growth settings for the datafile was changed from 100Mb increments to 25600%. The logfile is still the same at 200Mb as required. Both have unlimited growth.

Looking at sysfiles in the database, growth is 25600 for the log and data file. In Management Studio I cannot change the setting for the data file (log file still shows 200Mb growth increments), Studio tells me "Value 25600 is not valid for Value. Value should be between Minimum and Maximum.

Any thoughts? Cant really have a database sitting with this growth rate, and cannot change the value in sysfiles either (even with sp_configure etc).

Any ideas? Thanks

Have you tried clicking on the button next to the text that describes the growth. This provides the means to change the growth settings. The button has ellipse on it ...|||Seems to be a GUI problem. Try changing the autogrow value using ALTER DATABASE instead. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:19115900-bfb9-4dff-8042-9b7462a44781@.discussions.microsoft.com...
> Have a database, one filegroup with one datafile and one log file. >
> However, restarted the box and saw that the growth settings for the
> datafile was changed from 100Mb increments to 25600%. The logfile is
> still the same at 200Mb as required. Both have unlimited growth. >
> Looking at sysfiles in the database, growth is 25600 for the log and
> data file. In Management Studio I cannot change the setting for the data
> file (log file still shows 200Mb growth increments), Studio tells me
> "Value 25600 is not valid for Value. Value should be between Minimum and
> Maximum. >
> Any thoughts? Cant really have a database sitting with this growth rate,
> and cannot change the value in sysfiles either (even with sp_configure
> etc). >
> Any ideas? Thanks >
>|||

yeah, it's a GUI problem preventing me to change that value (I got that error from clicking the ... button to change it).

My other worry is what changed this, the system was set to grow in increments of 100Mb, settings omething to 25600% increase (when you cant even get to that value in the GUI) means something on the server changed ther settings.

|||This seems to be a bug on the SQL engine. I've had it happen twice now. First time I assumed I had done something wrong, reset the autogrow values to a number. This morning it has happened again and the server has no disk space left. 100% sql engine bug I think.|||

Further to this see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

The only workaround I have so far is to set Max size of the file to prevent the file from growing too large, or to turn off AutoGrow