Automatic Creation of LDF file

I was wondering how the automatic creation of a LDF (log)
file worked in MSSQL Server 2000. Is there a special
utility to use? I only have an MDF file and I try
to "Attach" it as a database. I get the following error
message:
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1813: Could not open new database 'DBName'. CREATE
DATABASE is aborted.
Device activation error. The physical file name 'C:\SQL
Data\DBName_log.ldf' may be incorrect.
--
OK
--
It's looking for the ldf file and it won't create it. How
do I get it to create a new log file? I tried creating a
new blank database with the same name and using it's ldf,
but somehow it knows they're not from the same database.
ThanksDanny,
May be there were some open transactions and its searching the ldf file for
matching.Was this database properly detached before?A prerequisite for
sp_attach* is that the database should be properly detached using
sp_detach_db.Either you can RESTORE from a valid and latest database backup
or try the workaround mentioned in :
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
The archived newsgroup thread
http://tinyurl.com/m071
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks|||Hi,
- Change your DB Status to Emergency Mode (32768).
- Stop SQL Server / Start SQL
- In query analyser, execute dbcc rebuild_log. REMEMBER: Rename old File Log
before.
- Change Your DB Status to Normal (0)
- Stop SQL Server / Start SQL
This error is because your GUID of file Log not match with definition of
your DB.
Regards,
Fabiano Maciel
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks

Autogrowth for MDF and LDF files

I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

CarlosI have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

Carlos

I generally allow dbs that are smaller that 20 GB to autogrow by the default setting (10%). But once they get above that size, I manage them manually and ensure that there is enough empty space in the datafile to get through until the next maintenance window.

Growing a data file in SQL 2005 is not as expensive (IO wise) as it was in SQL 2000, but I still think you want to keep a closer eye on things once they get above 20 GB. 20 GB is admittedly arbitrary. If you have space issues, you might consider a lower threshold.

Regards,

hmscott|||It depends on what your database is used for. Mine are configured to grow by several hundred MB\ a few GB but that is because there are small numbers of massive modifications. An OLTP database should not, IMHO, be growing that much each time. The user that submitted the modification that triggers a 500MB growth could be twiddling their thumbs for quite some time cursing the system as they do.

I too would manage the growth at peak periods with a view to eliminating\ reducing autogrowth as much as possible.|||Your file growth is because of your 500MB setting. When SQL starts to run out of space it will adjust by 500MB. If has to adjust twice a day, there's your 1GB. I think you're fine where you're at. Just make sure you have enough drive space. I'm sure your growth will plateau.