Clear/Purge Log files

The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink database"
but this does not seems to reduce the mdf or ldf filesize.
What I did is detach the database, delete the ldf file, re-attach the
database to create a new ldf file. If I do not do so, the application cannot
work (hang!) because the ldf file is too huge and it takes ages to commit a
transaction. Is there a "better" way to control the ldf file like
auto-purging ? Should I restrict the log file size to a specific filesize
like 500MB ? Does this mean it will auto-purge each time it reach 500MB for
the ldf file ?
ThanksThe way you manage your log file is driven by your database recovery plan.
If your recovery plan is to restore from your last full backup and not apply
transaction log backups, then change your database recovery model to SIMPLE.
This will keep your log size reasonable by removing committed transactions
from the log. The log will still need to be large enough to accommodate
your largest single transaction. If you want to reduce potential data loss,
you should use the BULK_LOGGED or FULL recovery model and backup your log
periodically.
The proper way to shrink files is with DBCC SHRINKFILE. See the Books
Online for details. You should not need to do this as part of routine
maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos" <wt_know@.hotmail.com> wrote in message
news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
database"
> but this does not seems to reduce the mdf or ldf filesize.
> What I did is detach the database, delete the ldf file, re-attach the
> database to create a new ldf file. If I do not do so, the application
cannot
> work (hang!) because the ldf file is too huge and it takes ages to commit
a
> transaction. Is there a "better" way to control the ldf file like
> auto-purging ? Should I restrict the log file size to a specific filesize
> like 500MB ? Does this mean it will auto-purge each time it reach 500MB
for
> the ldf file ?
> Thanks
>
>|||Hi,
Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-
Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateon
ly)
go
Alter database <dbname> set multi_user
After executing the above you can execute the below command check the log
file size and usage,
dbcc sqlperf(logspace)
Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos" <wt_know@.hotmail.com> wrote in message
> news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> database"
> cannot
commit[vbcol=seagreen]
> a
filesize[vbcol=seagreen]
> for
>|||Thanks for the advices ! :-)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MWo0lUPEHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Instead of detaching , delete the LDF file, Attach the database ytou
should
> have tried the below steps:-
> Alter database <dbname> set single_user with rollback immediate
> go
> backup log <dbname> to disk='d:\backup\dbname.trn1'
> go
> dbcc shrinkfile('logical_log_name',truncateon
ly)
> go
> Alter database <dbname> set multi_user
> After executing the above you can execute the below command check the log
> file size and usage,
> dbcc sqlperf(logspace)
> Like Dan suggested go for SIMPLE recovery model if your data is not
critical
> or you not require a transaction log based recovery (POINT_IN_TIME).
> Thanks
> Hari
> MCDBA
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
plan.[vbcol=seagreen]
> apply
> SIMPLE.
transactions[vbcol=seagreen]
> loss,
log[vbcol=seagreen]
> commit
> filesize
500MB[vbcol=seagreen]
>

Clear/Purge Log files

The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink database"
but this does not seems to reduce the mdf or ldf filesize.
What I did is detach the database, delete the ldf file, re-attach the
database to create a new ldf file. If I do not do so, the application cannot
work (hang!) because the ldf file is too huge and it takes ages to commit a
transaction. Is there a "better" way to control the ldf file like
auto-purging ? Should I restrict the log file size to a specific filesize
like 500MB ? Does this mean it will auto-purge each time it reach 500MB for
the ldf file ?
ThanksThe way you manage your log file is driven by your database recovery plan.
If your recovery plan is to restore from your last full backup and not apply
transaction log backups, then change your database recovery model to SIMPLE.
This will keep your log size reasonable by removing committed transactions
from the log. The log will still need to be large enough to accommodate
your largest single transaction. If you want to reduce potential data loss,
you should use the BULK_LOGGED or FULL recovery model and backup your log
periodically.
The proper way to shrink files is with DBCC SHRINKFILE. See the Books
Online for details. You should not need to do this as part of routine
maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos" <wt_know@.hotmail.com> wrote in message
news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
database"
> but this does not seems to reduce the mdf or ldf filesize.
> What I did is detach the database, delete the ldf file, re-attach the
> database to create a new ldf file. If I do not do so, the application
cannot
> work (hang!) because the ldf file is too huge and it takes ages to commit
a
> transaction. Is there a "better" way to control the ldf file like
> auto-purging ? Should I restrict the log file size to a specific filesize
> like 500MB ? Does this mean it will auto-purge each time it reach 500MB
for
> the ldf file ?
> Thanks
>
>|||Hi,
Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-
Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateonly)
go
Alter database <dbname> set multi_user
After executing the above you can execute the below command check the log
file size and usage,
dbcc sqlperf(logspace)
Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos" <wt_know@.hotmail.com> wrote in message
> news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> > The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
> database"
> > but this does not seems to reduce the mdf or ldf filesize.
> >
> > What I did is detach the database, delete the ldf file, re-attach the
> > database to create a new ldf file. If I do not do so, the application
> cannot
> > work (hang!) because the ldf file is too huge and it takes ages to
commit
> a
> > transaction. Is there a "better" way to control the ldf file like
> > auto-purging ? Should I restrict the log file size to a specific
filesize
> > like 500MB ? Does this mean it will auto-purge each time it reach 500MB
> for
> > the ldf file ?
> >
> > Thanks
> >
> >
> >
>|||Thanks for the advices ! :-)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MWo0lUPEHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Instead of detaching , delete the LDF file, Attach the database ytou
should
> have tried the below steps:-
> Alter database <dbname> set single_user with rollback immediate
> go
> backup log <dbname> to disk='d:\backup\dbname.trn1'
> go
> dbcc shrinkfile('logical_log_name',truncateonly)
> go
> Alter database <dbname> set multi_user
> After executing the above you can execute the below command check the log
> file size and usage,
> dbcc sqlperf(logspace)
> Like Dan suggested go for SIMPLE recovery model if your data is not
critical
> or you not require a transaction log based recovery (POINT_IN_TIME).
> Thanks
> Hari
> MCDBA
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> > The way you manage your log file is driven by your database recovery
plan.
> > If your recovery plan is to restore from your last full backup and not
> apply
> > transaction log backups, then change your database recovery model to
> SIMPLE.
> > This will keep your log size reasonable by removing committed
transactions
> > from the log. The log will still need to be large enough to accommodate
> > your largest single transaction. If you want to reduce potential data
> loss,
> > you should use the BULK_LOGGED or FULL recovery model and backup your
log
> > periodically.
> >
> > The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> > Online for details. You should not need to do this as part of routine
> > maintenance.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Carlos" <wt_know@.hotmail.com> wrote in message
> > news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> > > The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
> > database"
> > > but this does not seems to reduce the mdf or ldf filesize.
> > >
> > > What I did is detach the database, delete the ldf file, re-attach the
> > > database to create a new ldf file. If I do not do so, the application
> > cannot
> > > work (hang!) because the ldf file is too huge and it takes ages to
> commit
> > a
> > > transaction. Is there a "better" way to control the ldf file like
> > > auto-purging ? Should I restrict the log file size to a specific
> filesize
> > > like 500MB ? Does this mean it will auto-purge each time it reach
500MB
> > for
> > > the ldf file ?
> > >
> > > Thanks
> > >
> > >
> > >
> >
> >
>

Cleaning up data files when removing instance (integr. inst.)

Hi everyone,

I'm trying to integrate SQLEXPRESS in a custom application ("the app"). On installation, a new instance ("myNewInstance") is created, and a new DB ("myDB") is created on that instance.

Upon de-installation of "the app", I remove the new instance. To that end, I'm calling the SQLEXPRESS setup like this:

Code Snippet

setup.exe /qb REMOVE=SQL_Engine INSTANCENAME=myNewInstance

The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:

Code Snippet

Msg 5170, Level 16, State 1, Line 1
Cannot create file '[...]\MSSQL.2\MSSQL\DATA\myDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.

So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?

Thanks in advance,
Thorsten

hi Thorsten,

AFAIK, the setup/uninstall wizard does not provide a way to delete user's database, and this usually is a good thing as user's databases are actually the "real important things" to be preserved.. a setup option, SAVESYSDB=0/1 extend that feature to system databases as well, that are usually deleted in normal uninstall situations.. but "the contrary" is not available... but you can probably add a custom task to your uninstall (I do think via ORCA) to delete all "orphaned" remaining files...

regards

clean up bak files on file server

I have a maintanence plan that does a backup of all my databases and stores
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.
Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>
|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>
|||Its called "Maintenance Cleanup Task"
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:

> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
>
|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>
|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>

clean up bak files on file server

I have a maintanence plan that does a backup of all my databases and stores
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan I
>>have is not removing the .bak files that are older then 4 days. How can I
>>get my Clean up plan be able to delete the .bak files that are 4 days old?
>>This is only happening with my SQL 2005 servers, my sql 2000 servers are
>>working correctly.
>>
>|||Its called "Maintenance Cleanup Task"
--
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:
> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Mike,
> >
> > In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> > Cleanup Task. The expiry date on backups only marks when they can be
> > overwritten. The cleanup task is what actually deletes files.
> >
> > RLF
> >
> > "Mike" <Mike@.Ihatespam.net> wrote in message
> > news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
> >>I have a maintanence plan that does a backup of all my databases and
> >>stores the databases on a nas device. The problem is that the clean plan I
> >>have is not removing the .bak files that are older then 4 days. How can I
> >>get my Clean up plan be able to delete the .bak files that are 4 days old?
> >>This is only happening with my SQL 2005 servers, my sql 2000 servers are
> >>working correctly.
> >>
> >>
> >>
> >
> >
>
>|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How can
>>I get my Clean up plan be able to delete the .bak files that are 4 days
>>old? This is only happening with my SQL 2005 servers, my sql 2000 servers
>>are working correctly.
>>
>>
>|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following
>> purpose:
>> The History Cleanup task deletes historical data about Backup and
>> Restore, SQL Server Agent, and Maintenance Plan operations. This wizard
>> allows you to specify the type and age of the data to be deleted.
>> In case that is not clear (and I can understand why it might not be) it
>> is not deleting backups, but is deleting the history records that track
>> your backups, restores, and so forth. It is cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean
>>plan I have is not removing the .bak files that are older then 4 days.
>>How can I get my Clean up plan be able to delete the .bak files that
>>are 4 days old? This is only happening with my SQL 2005 servers, my sql
>>2000 servers are working correctly.
>>
>>
>>
>>
>|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||As of sp1, the cleanup task has an option to traverse subdirectories.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.Ihatespam.net> wrote in message news:uuFilmheIHA.4144@.TK2MSFTNGP05.phx.gbl...
>I created a clean up task and its still not working. I have all of my .bak files in sub folders
>with the db name, and I noticed that the task is only looking in a root directory with the .bak
>files, is there a way to have it search all sub folders within a directory?
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following purpose:
>> The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and
>> Maintenance Plan operations. This wizard allows you to specify the type and age of the data to
>> be deleted.
>> In case that is not clear (and I can understand why it might not be) it is not deleting backups,
>> but is deleting the history records that track your backups, restores, and so forth. It is
>> cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance Cleanup Task. The
>> expiry date on backups only marks when they can be overwritten. The cleanup task is what
>> actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and stores the databases on a
>>nas device. The problem is that the clean plan I have is not removing the .bak files that are
>>older then 4 days. How can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000 servers are working
>>correctly.
>>
>>
>>
>>
>

Clean Log files

Hi All,
I have problem with my logs files, they are going so much especially when I
import data.
The best way that I found to clean the log files is:
1. Detach database
2. Delete the log file from the data folder
3. Attach database
Obviously I can't do this during the day until nobody is using the database.
I have 16 databases.
Can I do this using an script?
Any help will be appreciate... tks in advance.
JFB> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
That may well be the best way to corrupt your data. The best way to
control log size is to choose the right recovery model, allocate a
sensible size to start with and then take regular Log and Database
backups as appropriate. If you are still in development then perhaps
database backups are sufficienct in which case choose the Simple
Recovery model.
David Portas
SQL Server MVP
--
http://support.microsoft.com/?kbid=873235|||Much of http://www.aspfaq.com/2446 applies to all databases, not just
tempdb.
Also see http://www.aspfaq.com/2471
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||By "cleaning" do you mean truncating the log or reducing the physical size?
If so, first check the recovery model set for your database.
If logs are not required to be maintained, then you can simply issue a
BACKUP LOG statement with NO_LOG option. To reduce the physical file size,
use DBCC SHRINKFILE. Details of these commands with examples can be found in
SQL Server Books Online.
Anith|||If you are not using BCP.EXE or the BULK INSERT statement to import your
data, then you should consider this. In SQL Server Books Online, read the
article titled "Logged and Minimally Logged Bulk Copy Operations".
Basically, the requirements for non-logged bulk copy operations are:
- The recovery model is simple or bulk-logged.
- The target table is not being replicated.
- The target table does not have any triggers.
- The target table has either 0 rows or no indexes.
- The TABLOCK hint is specified.
If this is an OLTP database, you can use the ALTER DATABASE.. statment to
switch between different recovery models as needed.
You may also want to consider dropping indexes (especially the clustered
index) before the import and re-creating them again afterward. This will
also have the affect of preventing index fragmentation and increasing the
load performance.
If this database is a 24x7 transactional system with many users, then you
may want to re-consider the process by which you import new data and avoid
massive import operations all together.
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||Hi,
Looks like you do not want the Transaction log backup. There are 2 ways to
clear the Log file online.
1. Take the transaction log backup and then shrink the LDF file.
Script:
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
2. Truncate the Transaction log and Shrink.
Script:
Backup log <dbname> with Truncate_only
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
Thanks
Hari
SQL Server MVP
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>

Circular dependencies from constraints

Hey!

I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.

To achieve this ive created te following tables (truncated for clearity):

###################
# Sites #
###################
# ID [Int] #
# ... #
###################

###################
# Folders #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

###################
# Files #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!

Then i set up the foreign constraints as follows:
Folders.FolderD -> Folders.ID
Folders.SiteID -> Sites.ID
Files.FolderID -> Folders.ID
Files.SiteID -> Sites.ID

All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)

Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?

Hi Supermajs,

Firslty, I would remove the constraints.

Secondly, remove the SiteID and FolderID fields from the Folders and Files tables.

Thirdly, add the fields ParentID and ParentTypeID to the Folders and Files tables.

Then add a new table called tblParentType with the following records: -

ParentTypeID ParentType

1 Site

2 Folder

Then add a constraint between ParentType.ParentTypeID and Folders.ParentTypeID and between ParentTypeID.ParentTypeID and Files.ParentTypeID.

Now a record within Folders will look like this: -

ID ParentID ParentTypeID

1 3 1 this folder is a folder under site id 3

2 14 2 this folder is a folder under folder id 14

The same applies for the Files table.

By doing it this way, you can easily add new levels without needing extra fields that always contain NULL.

For example, you could add a new layer above site called server. The ParentType table would be as follows: -

ParentTypeID ParentType

1 Server

2 Site

3 Folder

|||

.In relational modeling it is files and association, I think you have designed flat files that could be a problem because DRI(declarative referential integrity) comes with restrictions. If you have 50 files that are associated they belong together that is how you can start with 100 files and end up with 5 tables. Try the Normalization tutorial and free data models to clean up your design and post again so I can help you with the DRI Cascade operation because it comes with fixed requirement of if A references B then B must exist. Hope this helps

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

http://www.databaseanswers.org/data_models/

Choose MSDE install location?

I'm trying to install MSDE at c:\Program Files\Microsoft\SQL Server\ (as
opposed to ...\Microsoft SQL Server\).
I tried using the DATADIR= and TARGETDIR= options, but they don't seem to
affect the COM and Tools folders (which always get installed at
...\Microsoft SQL Server\80\). I don't see any other options to set the
location for those two folders.
Am i missing something, or am i stuck with keeping the COM and Tools
directories where they are?
/
hi,
I HATE PDAs wrote:
> I'm trying to install MSDE at c:\Program Files\Microsoft\SQL Server\ (as
> opposed to ...\Microsoft SQL Server\).
> I tried using the DATADIR= and TARGETDIR= options, but they don't
> seem to affect the COM and Tools folders (which always get installed
> at ...\Microsoft SQL Server\80\). I don't see any other options to
> set the location for those two folders.
> Am i missing something, or am i stuck with keeping the COM and Tools
> directories where they are?
you do not have control over a "little" part of the installations... this
with regard to all binaries that will be shared among all available SQL
Server / MSDE 2000 instances... these shared binaries will always be
installed there, and present at the higher service pack level..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF creat
ed.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the ND
F
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF created.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the NDF
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!
Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

Checkpoints: FTP task download FTP files fails in between then what will happen?

Hi,

I have a FTP task in my control flow that download files from a FTP server. This ftp task is inside a foreach container that loops over a ADO recordset for the file name. The files that the ftp task pulls are huge. If the FTP task fails then I want the FTP task to restart and only download those files that have not been downloaded. Is this possible?

What possible configurations do I have to make to the foreach container and the filetask?

Thanks a lot in advance for your help and time.

Regards,

$wapnil

Experts!!!! any inputs on this.

Thanks in advance for your time.

Regards,

$wapnil

|||

anyone.....help!

Thanks,

$wapnil

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon Whale
Hi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

Checking to see if SQL Server on machine is up and running

Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>

Checking to see if a records exists before inserting - 3 million + rows

I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.

I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.

To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.

Any thoughts would be great!

David Wynne wrote:

Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

You aren't doing a select * against the lookup table, are you?|||Of course not - just pseudo code to get across what I think I'd like to achieve.
|||

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

|||

Charles Talleyrand wrote:

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

Yep, the OP could use an Execute SQL task to load up a temporary table with the keys of the lookup table. Then, in the data flow, you can use the mentioned outer join to capture new records. That is, unless you need to repopulate the lookup table with the incoming "new" records before the next lookup.|||

David,

How many columns do you need to put in the lookup transformation to determine if a record exists? Unless we are taking about too many/wide columns; I don't see several millions to be a problem. Another option is to use partial cache with decent amount of memory; so the chances a row is not in cache is low.

Remember, always provide a query with only the columns that are strictly required for the join

checking for control files in SSIS

have a job that loads data from a data file into a table.

Is there a "task" that can be used to check to see if a file exists

and put error handling around it without programming ?

have you considered using the file watcher task? http://www.sqlis.com/default.aspx?23

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

check version of sql 2005

how do i check whether i have installed 64 bit /32 bit sql server 2005... if i see Program files and Program files (x86) directory can i be sure...

one more query..Is windows 2003 enterprise edition 64 bit Itanium based operating systems

Thanks in advance

Is this really a SSIS question or the SQL engine?

The standard SELECT/PRINT @.@.VERSION command works here for the SQL engine, just look in the output for x64 vs x86, or Itanium I presume, but cannot confirm, e.g.

Microsoft SQL Server 2005 - 9.00.2153.00 (X64)
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)

The presence of the Program Files (x86) directory means you are running on a 64-bit OS with WOW support. The 64-bit applications are installed in the alternative Program Files, so the presence of SQL in there would indicate you have a 64-bit install of SQL as well.

Windows 2003 Enterprise Edition comes in Itanium, x64 and x86 flavours.

|||

Is there a mapping table available between @.@.version string and the SQL Server 2005?

For example,

SQL Server 2005 RTM x86 (32-bit) Enterprise Edition --> @.@.version=?

SQL Server 2005 RTM x64 (32-bit) Enterprise Edition --> @.@.version =?

SQL Server 2005 RTM IA64 (64-bit) Enterprise Edition -->@.@.version=?

SQL Server 2005 with SP1 (32-bit) --> @.@.version=?

etc, etc.

I am particularly interested in the build number of SP1 vs. RTM so that I can build a process to check if SP1 has been deployed successfully or not.

Thanks

Sean

|||The "current" release with all the hot fixes is 9.00.2153. If you version is other, you need updates.

See: http://support.microsoft.com/kb/321185|||

SQL Server Version Database
(http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx)

The versions will also be included in any KB that accompanies a service pack or hotfix, for the latter at a file level as well, since not all fixes change the @.@.version result.

The edition is not considered part of the version (build number), but is included in the @.@.version result.

For SSIS, there are some more notes here, as off course you may have patched SSIS independently of the engine, or may not even have the engine installed-

Service Pack Versions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ServicePackVersions.html)