Auto created statistics and missing statistics

Hello group.

I have an issue, which has bothered me for a while now:

I'm wondering why the column statistics, which SQL Server wants me to
create, if I turn off auto-created statistics, are so important to the
optimizer?

Example: from Northwind (with auto create stats off), I do the following:

SELECT * FROM Customers WHERE Country = 'Sweden'

My query plan show a clustered index scan, which is expected - no index
exists for Country. BUT, the query plan also shows, that the optimizer is
missing a statistic on Country, which tells me, that the optimizer would
benefit from knowing this.

I cannot see why? (and I've been trying for a while now).

If I create the missing statistics, nothing happens in the query plan (and
why should it?). I could understand it, if the optimizer suggested an index
on Country - this would make sense, but if creating the missing index, query
analyzer creates the statistics with an empty index, which seems to me to be
less than usable.

I've been thinking long and hard about this, but haven't been able to reach
a conclusion :) It has some relevance to my work, because allowing the
optimizer to create missing statistics limits my options for designing
indexes (e.g. covering) for some rather wide tables, so I'm thinking why not
turn it off altogether. But I would like to know the consequences - hope
somebody has already delved into this, and knows a good explanation.

Rgds
Jesper"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> Hello group.
> I have an issue, which has bothered me for a while now:
> I'm wondering why the column statistics, which SQL Server wants me to
> create, if I turn off auto-created statistics, are so important to the
> optimizer?
> Example: from Northwind (with auto create stats off), I do the following:
> SELECT * FROM Customers WHERE Country = 'Sweden'
> My query plan show a clustered index scan, which is expected - no index
> exists for Country. BUT, the query plan also shows, that the optimizer is
> missing a statistic on Country, which tells me, that the optimizer would
> benefit from knowing this.
> I cannot see why? (and I've been trying for a while now).
> If I create the missing statistics, nothing happens in the query plan (and
> why should it?). I could understand it, if the optimizer suggested an
index
> on Country - this would make sense, but if creating the missing index,
query
> analyzer creates the statistics with an empty index, which seems to me to
be
> less than usable.
> I've been thinking long and hard about this, but haven't been able to
reach
> a conclusion :) It has some relevance to my work, because allowing the
> optimizer to create missing statistics limits my options for designing
> indexes (e.g. covering) for some rather wide tables, so I'm thinking why
not
> turn it off altogether. But I would like to know the consequences - hope
> somebody has already delved into this, and knows a good explanation.
> Rgds
> Jesper

http://msdn.microsoft.com/library/d...l/statquery.asp

Simon|||Thanks, Simon, informative article, but ...

... it doesn't really explain the stuff, that I wrote. The closest I get to
an explanation, when reading this is 'These statistics are created for
columns where the optimizer would have to estimate the approximate density
or distribution otherwise'.

I knew this, but I still do not know, why the optimizer needs to know the
density and/or distribution?? I can see no valid reason, and therefore I can
see no good reason for enabling auto-creation of stats.

What I probably looking for is a good example, where the use of an
automatically created stat saves time, cycles and IOs :)

Best Rgds - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4072f05a$1_2@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> > Hello group.
> > I have an issue, which has bothered me for a while now:
> > I'm wondering why the column statistics, which SQL Server wants me to
> > create, if I turn off auto-created statistics, are so important to the
> > optimizer?
> > Example: from Northwind (with auto create stats off), I do the
following:
> > SELECT * FROM Customers WHERE Country = 'Sweden'
> > My query plan show a clustered index scan, which is expected - no index
> > exists for Country. BUT, the query plan also shows, that the optimizer
is
> > missing a statistic on Country, which tells me, that the optimizer would
> > benefit from knowing this.
> > I cannot see why? (and I've been trying for a while now).
> > If I create the missing statistics, nothing happens in the query plan
(and
> > why should it?). I could understand it, if the optimizer suggested an
> index
> > on Country - this would make sense, but if creating the missing index,
> query
> > analyzer creates the statistics with an empty index, which seems to me
to
> be
> > less than usable.
> > I've been thinking long and hard about this, but haven't been able to
> reach
> > a conclusion :) It has some relevance to my work, because allowing the
> > optimizer to create missing statistics limits my options for designing
> > indexes (e.g. covering) for some rather wide tables, so I'm thinking why
> not
> > turn it off altogether. But I would like to know the consequences - hope
> > somebody has already delved into this, and knows a good explanation.
> > Rgds
> > Jesper
>
http://msdn.microsoft.com/library/d...l/statquery.asp
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> Thanks, Simon, informative article, but ...
> ... it doesn't really explain the stuff, that I wrote. The closest I get
to
> an explanation, when reading this is 'These statistics are created for
> columns where the optimizer would have to estimate the approximate density
> or distribution otherwise'.
> I knew this, but I still do not know, why the optimizer needs to know the
> density and/or distribution?? I can see no valid reason, and therefore I
can
> see no good reason for enabling auto-creation of stats.
> What I probably looking for is a good example, where the use of an
> automatically created stat saves time, cycles and IOs :)
> Best Rgds - Jesper

OK, here's another informative article :-)

http://www.winnetmag.com/SQLServer/...2075/22075.html

In summary, index statistics exist only for the first column in an index,
but auto-created (or manually created) statistics can exist for any column.
This gives the optimizer extra information, which might mean it chooses a
different, more efficient index for a query.

Check out the example on the second page of the article - on my system, this
reduced the logical reads required for the query from 104 to 43.

But you're correct to consider that there can be an impact on performance in
some situations:

http://support.microsoft.com/defaul...kb;en-us;195565

Simon|||Thanks, Simon, that one did the trick.

One less mystery.

On my machine, QA tells me that the two queries (the index scan on
ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
respectively. I would argue, that this saving is not worth the 'used up
index space'. In my professional life, I've seen tables, which are wide
enough (200+ columns) to demand, that precious index space is saved.

Basically, I think there are too many 'ifs' before an auto-created index
saves performance, but I appreciate the optimization idea behind it.

Thanks - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4073121e_1@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> > Thanks, Simon, informative article, but ...
> > ... it doesn't really explain the stuff, that I wrote. The closest I get
> to
> > an explanation, when reading this is 'These statistics are created for
> > columns where the optimizer would have to estimate the approximate
density
> > or distribution otherwise'.
> > I knew this, but I still do not know, why the optimizer needs to know
the
> > density and/or distribution?? I can see no valid reason, and therefore I
> can
> > see no good reason for enabling auto-creation of stats.
> > What I probably looking for is a good example, where the use of an
> > automatically created stat saves time, cycles and IOs :)
> > Best Rgds - Jesper
> OK, here's another informative article :-)
> http://www.winnetmag.com/SQLServer/...2075/22075.html
> In summary, index statistics exist only for the first column in an index,
> but auto-created (or manually created) statistics can exist for any
column.
> This gives the optimizer extra information, which might mean it chooses a
> different, more efficient index for a query.
> Check out the example on the second page of the article - on my system,
this
> reduced the logical reads required for the query from 104 to 43.
> But you're correct to consider that there can be an impact on performance
in
> some situations:
> http://support.microsoft.com/defaul...kb;en-us;195565
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message news:<40732877$0$274$edfadb0f@.dread12.news.tele.dk>...
> Thanks, Simon, that one did the trick.
> One less mystery.
> On my machine, QA tells me that the two queries (the index scan on
> ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
> respectively. I would argue, that this saving is not worth the 'used up
> index space'. In my professional life, I've seen tables, which are wide
> enough (200+ columns) to demand, that precious index space is saved.
> Basically, I think there are too many 'ifs' before an auto-created index
> saves performance, but I appreciate the optimization idea behind it.
> Thanks - Jesper

<snip
Well, you have to be careful about reaching conclusions based on
simple queries using small data sets. It's possible that a complex
join involving millions of rows would give a more significant
difference. To get a definite answer for your environment, you would
have to do some benchmarking, with and without statistics.

Simon

Attribute Relationship ?

I think understand how and why we need to setup attribute relationship but I'm probably missing one important thing here...

If I have the following hierarchies in my dimension:

Circulaire > Segment > Promotion

Circulaire > Promotion

Logically I should have defined my attribute relationship like this:

Circulaire

Segment

- Circulaire

Promotion

- Segment

- Circulaire

Promotion Key

- Promotion

This result in the following error:

This dimension contains one or more redundant attribute relationships. These relationships may prevent data from being aggregated when a non-key attribute is used as a granularity attribute in a cube. Verify the following relationships and delete those that are not needed: [Code Promotion] -> [Promotion - Circulaire].

What is the best practice to manage those issues with attribute relationship? I want to make sure that all my hierarchies are designed for best performance.

On the same note how should we set-up attribute relationship when multiple hierarchies are using the same level in different order?

LEVEL 1 > LEVEL 2 > LEVEL 3 > LEVEL 4

LEVEL 1 > LEVEL 3 > LEVEL 2 > LEVEL 4

thanks,

In the 1st scenario, since Segment directly relates to Promotion and Circulaire directly relates to Segment, relating Circulaire to Promotion is redundant. This is similar to a Year->Month->Day hierarchy, where relating Year to Day directly would be redundant.

In the 2nd scenario, I assume that there are 2 alternate hierarchies for user navigation. They can't both be natural (strong) hierachies (unless there is a strict 1:1 relationship between Level 2 and Level 3 members). So attribute relationships should only reflect strict functional dependencies, not navigational convenience. This paper discusses attribute relationships in more detail:

http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm

|||Thank you

Attaching SQL 2000 database (.mdf) with missing (.ldf) file

Hello

Please can anyone help me

I've been given a .mdf file from sql server 2000 which i need to attach but we do not have the .ldf file

I am unable to recover the old .ldf file

I have tried the attach single file command but obvioulsy this still looks for the old .ldf file path which no longer exists

any help would be mostly appriciated

many thanks

Martin

sp_attach_single_file_db only works if the database was appropiatly closed and concistent. If the procedure does not work, you will either need to have the ldf file or a valid backup.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Unfortunatly we do not have the ldf file or a valid backup

we only have the mdf file

is there no way of getting round this or recreating a log so i can access the data of the mdf

regards

Martin

|||Hi, I'm in exactly the same situation where a server failure has lost us the LDF but we still have an MDF..

Weve tried the various attach etc but still no luck so if anyone has any advice I'd sure appreciate it as well please...........

regards
PeterSm
|||Unfortunately, the tools mentioned are the only ones.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Attaching SQL 2000 database (.mdf) with missing (.ldf) file

Hello

Please can anyone help me

I've been given a .mdf file from sql server 2000 which i need to attach but we do not have the .ldf file

I am unable to recover the old .ldf file

I have tried the attach single file command but obvioulsy this still looks for the old .ldf file path which no longer exists

any help would be mostly appriciated

many thanks

Martin

sp_attach_single_file_db only works if the database was appropiatly closed and concistent. If the procedure does not work, you will either need to have the ldf file or a valid backup.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Unfortunatly we do not have the ldf file or a valid backup

we only have the mdf file

is there no way of getting round this or recreating a log so i can access the data of the mdf

regards

Martin

|||Hi, I'm in exactly the same situation where a server failure has lost us the LDF but we still have an MDF..

Weve tried the various attach etc but still no luck so if anyone has any advice I'd sure appreciate it as well please...........

regards
PeterSm|||Unfortunately, the tools mentioned are the only ones.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Attaching SQL 2000 database (.mdf) with missing (.ldf) file

Hello

Please can anyone help me

I've been given a .mdf file from sql server 2000 which i need to attach but we do not have the .ldf file

I am unable to recover the old .ldf file

I have tried the attach single file command but obvioulsy this still looks for the old .ldf file path which no longer exists

any help would be mostly appriciated

many thanks

Martin

sp_attach_single_file_db only works if the database was appropiatly closed and concistent. If the procedure does not work, you will either need to have the ldf file or a valid backup.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Unfortunatly we do not have the ldf file or a valid backup

we only have the mdf file

is there no way of getting round this or recreating a log so i can access the data of the mdf

regards

Martin

|||Hi, I'm in exactly the same situation where a server failure has lost us the LDF but we still have an MDF..

Weve tried the various attach etc but still no luck so if anyone has any advice I'd sure appreciate it as well please...........

regards
PeterSm
|||Unfortunately, the tools mentioned are the only ones.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Attaching Database if log files missing

Hi.
Is it possible to attach a database if the log files have been lost?
I had a database with 1 .mdf file and 2 .ldf (or whatever is the extension
for the log file).
The oldest log fie has been lost (corrupted HD), so I wonder if it is possib
le to attach the database even if that file is missing (using the GU interfa
ce it seems impossible, but maybe there is a workaround).
Any suggestion?
Thank you,
MicheleHi,
Try to attach the database with a single mdf file using the below stored
procedure
sp_attach_single_file_db 'DBNAME', 'physical file name'
Thanks
Hari
MCDBA
"Michele" <anonymous@.discussions.microsoft.com> wrote in message
news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> Hi.
> Is it possible to attach a database if the log files have been lost?
> I had a database with 1 .mdf file and 2 .ldf (or whatever is the
extension for the log file).
> The oldest log fie has been lost (corrupted HD), so I wonder if it is
possible to attach the database even if that file is missing (using the GU
interface it seems impossible, but maybe there is a workaround).
> Any suggestion?
> Thank you,
> Michele|||I would be surprised if Hari's sp_attach_single_file_db suggestion works
here because of the multiple log files. Even with a single log file, an
attach may not work when the database was not cleanly detached.
Your best option is to restore from backup. If you have no backup and need
to salvage data, contact Microsoft PSS. There is some trickery you can use
to get the database online without the original log files but physical and
logical database integrity is questionable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Michele" <anonymous@.discussions.microsoft.com> wrote in message
news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> Hi.
> Is it possible to attach a database if the log files have been lost?
> I had a database with 1 .mdf file and 2 .ldf (or whatever is the
extension for the log file).
> The oldest log fie has been lost (corrupted HD), so I wonder if it is
possible to attach the database even if that file is missing (using the GU
interface it seems impossible, but maybe there is a workaround).
> Any suggestion?
> Thank you,
> Michele|||Hi Dan,
Thanks for pointing it out. In this case of multiple Log files Michele need
to either contact MS PSS or restore from latest backup.
Thanks
Hari
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:egMrizw8DHA.2812@.TK2MSFTNGP11.phx.gbl...
> I would be surprised if Hari's sp_attach_single_file_db suggestion works
> here because of the multiple log files. Even with a single log file, an
> attach may not work when the database was not cleanly detached.
> Your best option is to restore from backup. If you have no backup and
need
> to salvage data, contact Microsoft PSS. There is some trickery you can
use
> to get the database online without the original log files but physical and
> logical database integrity is questionable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michele" <anonymous@.discussions.microsoft.com> wrote in message
> news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> extension for the log file).
> possible to attach the database even if that file is missing (using the GU
> interface it seems impossible, but maybe there is a workaround).
>|||Hi,
thanks to you both.
Unfortunately I do not have any backup, that's why I needed a way to reattac
h the database using the "raw" files.
I will try to contact MS PSS as you suggested.
Thanks,
Michele

Attaching Database if log files missing

Hi
Is it possible to attach a database if the log files have been lost
I had a database with 1 .mdf file and 2 .ldf (or whatever is the extension for the log file)
The oldest log fie has been lost (corrupted HD), so I wonder if it is possible to attach the database even if that file is missing (using the GU interface it seems impossible, but maybe there is a workaround)
Any suggestion
Thank you
MicheleHi,
Try to attach the database with a single mdf file using the below stored
procedure
sp_attach_single_file_db 'DBNAME', 'physical file name'
Thanks
Hari
MCDBA
"Michele" <anonymous@.discussions.microsoft.com> wrote in message
news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> Hi.
> Is it possible to attach a database if the log files have been lost?
> I had a database with 1 .mdf file and 2 .ldf (or whatever is the
extension for the log file).
> The oldest log fie has been lost (corrupted HD), so I wonder if it is
possible to attach the database even if that file is missing (using the GU
interface it seems impossible, but maybe there is a workaround).
> Any suggestion?
> Thank you,
> Michele|||I would be surprised if Hari's sp_attach_single_file_db suggestion works
here because of the multiple log files. Even with a single log file, an
attach may not work when the database was not cleanly detached.
Your best option is to restore from backup. If you have no backup and need
to salvage data, contact Microsoft PSS. There is some trickery you can use
to get the database online without the original log files but physical and
logical database integrity is questionable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Michele" <anonymous@.discussions.microsoft.com> wrote in message
news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> Hi.
> Is it possible to attach a database if the log files have been lost?
> I had a database with 1 .mdf file and 2 .ldf (or whatever is the
extension for the log file).
> The oldest log fie has been lost (corrupted HD), so I wonder if it is
possible to attach the database even if that file is missing (using the GU
interface it seems impossible, but maybe there is a workaround).
> Any suggestion?
> Thank you,
> Michele|||Hi Dan,
Thanks for pointing it out. In this case of multiple Log files Michele need
to either contact MS PSS or restore from latest backup.
Thanks
Hari
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:egMrizw8DHA.2812@.TK2MSFTNGP11.phx.gbl...
> I would be surprised if Hari's sp_attach_single_file_db suggestion works
> here because of the multiple log files. Even with a single log file, an
> attach may not work when the database was not cleanly detached.
> Your best option is to restore from backup. If you have no backup and
need
> to salvage data, contact Microsoft PSS. There is some trickery you can
use
> to get the database online without the original log files but physical and
> logical database integrity is questionable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michele" <anonymous@.discussions.microsoft.com> wrote in message
> news:5A5320A4-57C5-4C47-9940-4D1D89D5FF25@.microsoft.com...
> > Hi.
> > Is it possible to attach a database if the log files have been lost?
> > I had a database with 1 .mdf file and 2 .ldf (or whatever is the
> extension for the log file).
> > The oldest log fie has been lost (corrupted HD), so I wonder if it is
> possible to attach the database even if that file is missing (using the GU
> interface it seems impossible, but maybe there is a workaround).
> > Any suggestion?
> > Thank you,
> > Michele
>|||Hi
thanks to you both
Unfortunately I do not have any backup, that's why I needed a way to reattach the database using the "raw" files
I will try to contact MS PSS as you suggested
Thanks
Michele

Attaching a db - missing log (ldf)

Hello,

I'm trying to re-attach sqlserver 2005 db. I have the mdf file but I don't have the log file. When I try to attach, I get the appended error.

Is there anyway, around this?

Thanks

Houman

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'SERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down. (Microsoft SQL Server, Error: 1813)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1813&LinkId=20476

BUTTONS:

OK

I also tried:

CREATE DATABASE fuzzy

ON (NAME='fuzzy', FILENAME='C:\temp\fuzzy\fuzzy.mdf')

FOR ATTACH_REBUILD_LOG

And recieved the error:

File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.

Boy, this doesn't look good...anyway around this?

Thanks

Houman

|||

The database which u r trying to attach should be properly detached one. otherwise this will not work.

Madhu

|||The file you are trying to attach was not "detatched" first and will never work. You need to either detach it properly and then copy it, or restore from backup.

|||

I'm not a dba, I was following some dude's instructions on truncating the log file. I deattched the db using the GUI. Then deleted the log file (it was over 5 gb).

Is it possible that the cause is something else?

Thanks,

Houman

|||

if it was properly detached then this error will not occur... anyhow read sp_attach_single_file_db in BOL and try this sytem stroprocedure... But let me tell u , this is not the recommended way do shrink the log file. What u should do is

Backup database Somedatabase with truncate_only --

WITH 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.

DBCC SHRINKFILE (somelogical Log file Name, 100)

refer : http://support.microsoft.com/kb/873235

Read Shrinking the Transaction Log in BOL

|||We have found doing that procedure under 2005 does not work if you have more than 1 log file. It will not recreate more than 1 log.

If you detached it, you should not be getting that message. You are probably going to have to restore from backup.

In the future the best way to shrink the log file is to use the "shrink" method. You can do this automatically by setting up a "maintenance plan".

|||Connection of a file .mdf without a file .ldf

This page in Russian to translate it is possible here|||

Dear, the Article In Russian Is just for SQL SERVER 2000

it's not for SQL SERVER 2005

so, Can any body help me here?

I didn't dettach the data base and the log file was deleted....

oh....

I don't want to cry on it but is there an way to restor it....

I know it wasn't clearly shut down......

I used multiple ways and it didn't work....

for example

:

USE [master];

GO

Create DATABASE [pcrm]

ON (FILENAME = 'E:\pcrm.mdf')

FOR ATTACH_REBUILD_LOG

go

and this what I get every time:

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 2

Could not open new database 'pcrm'. CREATE DATABASE is aborted.

I even used the way in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=645631&SiteID=1 in despite of that I know it's for SQL SERVER 2000

and this gave me some error message I can not talk about

PLEASE HELP ME!!!,Thanks in advance....

|||

you can make use of this,

sp_attach_single_file_db [ @.dbname= ] 'dbname' , [ @.physname= ] 'physical_name'but its not possible for multiple data files.....also it wont work if the db was not clearly shudown

if that is not yielding the desired result try as mentioned below,currently if your db might be in suspect state, but it will not be shown in SSMS if thats the case,then start from step(f), if this also fails perform from step (a) to step(h)

(a) create a database with the same name in another directory as the one you're trying to attach
(b) re-create all filesgroups and files as necessary
(c) shutdown the server
(d) swap in the old mdf file and any ndf files
(e) bring up the server and let the database attempt to be recovered and then go into suspect mode
(f) put the database in single_user and emergency modes
(g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
(h) return database to online, multi_user mode

|||dear Mr.Deepak Rangarajan
i don't know how to thank you...
you really helped me....
it's done now....
the first and second solution didn't work with me but
doing all the steps from a to h
comes with the desired results....
now it's done.....
I advice all those who have the same problem to do this method and never listen to those that say it's impossible
Mr.Deepak Rangarajan
thank you again..

Attaching a db - missing log (ldf)

Hello,

I'm trying to re-attach sqlserver 2005 db. I have the mdf file but I don't have the log file. When I try to attach, I get the appended error.

Is there anyway, around this?

Thanks

Houman

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'SERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down. (Microsoft SQL Server, Error: 1813)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1813&LinkId=20476

BUTTONS:

OK

I also tried:

CREATE DATABASE fuzzy

ON (NAME='fuzzy', FILENAME='C:\temp\fuzzy\fuzzy.mdf')

FOR ATTACH_REBUILD_LOG

And recieved the error:

File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.

Boy, this doesn't look good...anyway around this?

Thanks

Houman

|||

The database which u r trying to attach should be properly detached one. otherwise this will not work.

Madhu

|||The file you are trying to attach was not "detatched" first and will never work. You need to either detach it properly and then copy it, or restore from backup.|||

I'm not a dba, I was following some dude's instructions on truncating the log file. I deattched the db using the GUI. Then deleted the log file (it was over 5 gb).

Is it possible that the cause is something else?

Thanks,

Houman

|||

if it was properly detached then this error will not occur... anyhow read sp_attach_single_file_db in BOL and try this sytem stroprocedure... But let me tell u , this is not the recommended way do shrink the log file. What u should do is

Backup database Somedatabase with truncate_only --

WITH 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.

DBCC SHRINKFILE (somelogical Log file Name, 100)

refer : http://support.microsoft.com/kb/873235

Read Shrinking the Transaction Log in BOL

|||We have found doing that procedure under 2005 does not work if you have more than 1 log file. It will not recreate more than 1 log.

If you detached it, you should not be getting that message. You are probably going to have to restore from backup.

In the future the best way to shrink the log file is to use the "shrink" method. You can do this automatically by setting up a "maintenance plan".|||Connection of a file .mdf without a file .ldf

This page in Russian to translate it is possible here|||

Dear, the Article In Russian Is just for SQL SERVER 2000

it's not for SQL SERVER 2005

so, Can any body help me here?

I didn't dettach the data base and the log file was deleted....

oh....

I don't want to cry on it but is there an way to restor it....

I know it wasn't clearly shut down......

I used multiple ways and it didn't work....

for example

:

USE [master];

GO

Create DATABASE [pcrm]

ON (FILENAME = 'E:\pcrm.mdf')

FOR ATTACH_REBUILD_LOG

go

and this what I get every time:

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 2

Could not open new database 'pcrm'. CREATE DATABASE is aborted.

I even used the way in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=645631&SiteID=1 in despite of that I know it's for SQL SERVER 2000

and this gave me some error message I can not talk about

PLEASE HELP ME!!!,Thanks in advance....

|||

you can make use of this,

sp_attach_single_file_db [ @.dbname= ] 'dbname'

, [ @.physname= ] 'physical_name'but its not possible for multiple data files.....also it wont work if the db was not clearly shudown

if that is not yielding the desired result try as mentioned below,currently if your db might be in suspect state, but it will not be shown in SSMS if thats the case,then start from step(f), if this also fails perform from step (a) to step(h)

(a) create a database with the same name in another directory as the one you're trying to attach
(b) re-create all filesgroups and files as necessary
(c) shutdown the server
(d) swap in the old mdf file and any ndf files
(e) bring up the server and let the database attempt to be recovered and then go into suspect mode
(f) put the database in single_user and emergency modes
(g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
(h) return database to online, multi_user mode

|||dear Mr.Deepak Rangarajan
i don't know how to thank you...
you really helped me....
it's done now....
the first and second solution didn't work with me but
doing all the steps from a to h
comes with the desired results....
now it's done.....
I advice all those who have the same problem to do this method and never listen to those that say it's impossible
Mr.Deepak Rangarajan
thank you again..

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie