Deleting records in the logfile

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

Thanks

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

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

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

Simon

Deleting or Clearing

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

Deleting log files

Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need any
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
Ant
Hi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant
|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>
|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:

> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>
|||> We backup every evening.
What types of backups?. Note that a database backup does not remove
uncommitted transactions from the log so the log files will grow
indefinitely. The only way to remove committed log data is with a LOG
backup or by keeping the database in the SIMPLE recovery model. In the FULL
or BULK_LOGGED model, one normally schedules periodic LOG backups in between
to full backups.
Once you've backed up your logs, you can shrink the log file back to a
reasonable size as Tibor suggested.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
|||Hello,
If your recovery model is FULL or BULK_LOGGED you need to perform
Transaction Log Backup in frequent intervals. This will make sure that LDF
file will not grow.
Follw the below steps:-
1. Schedule a Daily full backup once a day.
2. Schedule a Transaction Log backup every 30 minutes
3. After the completion of daily full database backup you could delete all
the previous transction log backup files
This approach will help you to recover the database fully.
If your current LDF file is huge then use DBCC SHRINKFILE to reduce the
size. Before shrink either truncate the Log or Backup the log.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
sql

Deleting log files

Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need an
y
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
AntHi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote
in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:

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

Deleting log files

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

Deleting log file in sql 2000 database

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

Deleting log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...

quote:

---

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

---

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

Deleting Log file

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

Deleting Log file

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

Deleting Log file

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

Deleting files

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

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

Deleting extra tempdb log and data files

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

Thanks much.

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

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

(assuming SQL 2000).

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

But, again, back up everything etc.

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

Assuming you have SQL2000, then this covers it:

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

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

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

sean

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

Deleting data bloats log file

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

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

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

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

TRUNCATE TABLE MyTable

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

|||Hi Arnie,

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

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

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

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

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

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

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

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

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

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

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

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

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

|||Thanks for the info.

I set the Recovery Model to simple.

I deleted about 95% of the data.

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

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

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

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

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

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

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

HTH!

|||Thanks Rich,

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

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

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

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

|||

Sorry if my wording was a bit confusing.

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

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

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

HTH!

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

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

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

I am getting this error message:

Incorrect syntax near 'MODIFY'.

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

Thats not the correct syntax for that command:

Try:

Code Snippet

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

Good luck!|||

Let me expand upon Rich's explanation.

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

Perhaps these resources will help:

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

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

|||Thanks again.

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

Deleting a Log file and recreating it...

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

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

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

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

Deleting a Log file and recreating it...

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

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

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

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

Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there
anyway to restore the database from the data file only?
ThanksSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||You can try sp_attach_single_file_db (see Books Online).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks|||Neither one of these methods is working for me. Any ideas why not?
The second one (which is much more simple) looks like this:
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'imBART'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LD
F' may be incorrect.|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> there anyway to restore the database from the data file only?
>|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> LDF
use[vbcol=seagreen]
>|||I don't have the error code right now, but I think that it was similar to th
e other error code about device activation...I would guess that the .mdf fil
e is corrupted.
I shall go back to my prior back-up and "Sin no more" ;-)
...a beginners mistake.
Thanks to everyone for your help.

Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there anyway to restore the database from the data file only
ThanksSee if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||You can try sp_attach_single_file_db (see Books Online).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks|||Neither one of these methods is working for me. Any ideas why not
The second one (which is much more simple) looks like this
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF
Server: Msg 1813, Level 16, State 2, Line
Could not open new database 'imBART'. CREATE DATABASE is aborted
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> > I inadvertently deleted my log file, but still have my data file...is
> there anyway to restore the database from the data file only?
> >
> > Thanks
>|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Follow the steps defined below to recover the database:-
> >
> > It seems your old database have mutiple LDF files. If you have mutilple
> LDF
> > files missed out you will not be able to use
> > the procedure sp_attach_single_file_db to attach the MDF file alone.
> >
> > A solution for this is:
> >
> > 1. Create a new database with the same name and same MDF and LDF files
> > 2. Stop sql server and rename the existing MDF to a new one and copy the
> > original MDF to this location and delete the LDF files.
> > 3. Start SQL Server
> > 4. Now your database will be marked suspect
> > 5. Update the sysdatabases to update to Emergency mode. This will not
use
> > LOG files in start up
> >
> > update sysdatabases set status=32768 where name ='dbname'
> >
> > 6. Restart sql server. now the database will be in emergency mode
> >
> > 7. Now execute the undocumented DBCC to create a log file
> >
> > DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> >
> > (replace the dbname and log file name based on ur requirement)
> >
> > 8. Execute sp_resetstatus <dbname>
> >
> > 9. Restart SQL server and see the database is online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> > "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> > news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> > > I inadvertently deleted my log file, but still have my data file...is
> > there anyway to restore the database from the data file only?
> > >
> > > Thanks
> >
> >
>|||I don't have the error code right now, but I think that it was similar to the other error code about device activation...I would guess that the .mdf file is corrupted
I shall go back to my prior back-up and "Sin no more" ;-
...a beginners mistake
Thanks to everyone for your help.