Take a look at the Execution Log topics in Books Online. All of the informaiton you want is stored in the ReportServer database. There is even a DTS package to extract the data and a set of reports which show you statistics.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
--Original Message--
From: Randy Knight
Posted At: Thursday, October 06, 2005 2:53 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Determine slow running reports
Subject: Re: Determine slow running reports
That would be great if I had control of the reports but as I said, the reports are published by analysts from all over the company. I just wondered if RS put some statistics or anything like that in the ReportServer database. It would seem to be useful ... how many times report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)Just what I was looking for. Thanks.
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Tuesday, March 27, 2012
Determine sequence in log
In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
any way to extract a pattern of rows? Specifically, I am trying to select al
l
records that have an audit_log_id of '13' which would be immediately followe
d
by a record with an audit_log_id of '16', which in turn would be immediately
followed by a record with an audit_log_id of '2', and display the results in
these groups of three records?
Thanks for any help.
SteveCan you post some ddl, sample data and expected result?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve B" wrote:
> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Only if there is some field, IN THE TABLE, that allows you to "determine" ro
w
sequence. I mean if there exists a ccolun, with unique values, which when
sorted, will sequence the rows in the order in which "immediately following
"
has the meaning you want it to have. If that's so, let's say that column i
s
named <LogDate>.
You have to join the table to itself, where, for each record in the first
instance of the table is "Joined" to it's Immediate Follower, by "Joining"
based on the value of LogDate being equal to the Minimum value of all the
records with LogDate > Than this records LogDate...
Select Prev.*, Next.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Thanks Alejandro.
Below is an example. I would like to return the sets of rows that are 13,
16, 2. THere are three examples in this table.
AL_EVENT_ID AL_DATETIME
2 9/24/04 9:16 AM
13 9/24/04 9:51 AM
2 9/24/04 10:21 AM
13 9/24/04 10:39 AM
13 9/24/04 1:17 PM
2 9/24/04 1:23 PM
13 9/24/04 2:05 PM
2 9/24/04 2:40 PM
2 9/24/04 2:43 PM
13 9/24/04 2:56 PM
2 9/29/04 8:13 AM
2 9/29/04 4:11 PM
2 9/30/04 8:32 AM
16 10/1/04 11:38 AM
13 10/1/04 12:28 PM
16 10/1/04 12:29 PM
2 10/1/04 12:29 PM
2 10/1/04 2:30 PM
2 10/1/04 2:46 PM
2 10/1/04 3:04 PM
13 10/1/04 4:40 PM
2 10/1/04 4:44 PM
13 10/1/04 4:58 PM
2 10/4/04 9:42 AM
13 10/4/04 3:23 PM
16 10/4/04 3:23 PM
2 10/4/04 3:23 PM
2 10/5/04 4:56 PM
2 10/6/04 9:35 AM
13 10/6/04 11:52 AM
13 10/6/04 12:47 PM
16 10/6/04 12:49 PM
2 10/6/04 12:49 PM
2 10/6/04 1:32 PM
13 10/6/04 4:06 PM
2 10/7/04 8:51 AM
2 10/7/04 11:39 AM
13 10/7/04 11:49 AM
"Alejandro Mesa" wrote:
> Can you post some ddl, sample data and expected result?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
>
> "Steve B" wrote:
>|||Thanks very much. I'm sure this is what I need. Not quite clear though on th
e
Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't match
.
I have added some example data to Alejandro's post.
Steve
"CBretana" wrote:
> Only if there is some field, IN THE TABLE, that allows you to "determine"
row
> sequence. I mean if there exists a ccolun, with unique values, which when
> sorted, will sequence the rows in the order in which "immediately followi
ng"
> has the meaning you want it to have. If that's so, let's say that column
is
> named <LogDate>.
> You have to join the table to itself, where, for each record in the first
> instance of the table is "Joined" to it's Immediate Follower, by "Joining"
> based on the value of LogDate being equal to the Minimum value of all the
> records with LogDate > Than this records LogDate...
> Select Prev.*, Next.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > First.LogDate)
> Join AUDIT_LOG Last On
> Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > Mid.LogDate)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
>
>
> "Steve B" wrote:
>|||Sorry they were table aliases in my first incantation, and neglected to
change them... They should be First, Mid, and Last
The SQL should be
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Using your actual column name,
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > First.AL_DATETIME)
Join AUDIT_LOG Last On
Last.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > Mid.AL_DATETIME)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Thank once again for your help. It works like a charm
Steve
"CBretana" wrote:
> Using your actual column name,
> Select First.*, Mid.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > First.AL_DATETIME)
> Join AUDIT_LOG Last On
> Last.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > Mid.AL_DATETIME)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
> "Steve B" wrote:
>|||Yr very welcome!
"Steve B" wrote:
> Thank once again for your help. It works like a charm
> Steve
> "CBretana" wrote:
>
any way to extract a pattern of rows? Specifically, I am trying to select al
l
records that have an audit_log_id of '13' which would be immediately followe
d
by a record with an audit_log_id of '16', which in turn would be immediately
followed by a record with an audit_log_id of '2', and display the results in
these groups of three records?
Thanks for any help.
SteveCan you post some ddl, sample data and expected result?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve B" wrote:
> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Only if there is some field, IN THE TABLE, that allows you to "determine" ro
w
sequence. I mean if there exists a ccolun, with unique values, which when
sorted, will sequence the rows in the order in which "immediately following
"
has the meaning you want it to have. If that's so, let's say that column i
s
named <LogDate>.
You have to join the table to itself, where, for each record in the first
instance of the table is "Joined" to it's Immediate Follower, by "Joining"
based on the value of LogDate being equal to the Minimum value of all the
records with LogDate > Than this records LogDate...
Select Prev.*, Next.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Thanks Alejandro.
Below is an example. I would like to return the sets of rows that are 13,
16, 2. THere are three examples in this table.
AL_EVENT_ID AL_DATETIME
2 9/24/04 9:16 AM
13 9/24/04 9:51 AM
2 9/24/04 10:21 AM
13 9/24/04 10:39 AM
13 9/24/04 1:17 PM
2 9/24/04 1:23 PM
13 9/24/04 2:05 PM
2 9/24/04 2:40 PM
2 9/24/04 2:43 PM
13 9/24/04 2:56 PM
2 9/29/04 8:13 AM
2 9/29/04 4:11 PM
2 9/30/04 8:32 AM
16 10/1/04 11:38 AM
13 10/1/04 12:28 PM
16 10/1/04 12:29 PM
2 10/1/04 12:29 PM
2 10/1/04 2:30 PM
2 10/1/04 2:46 PM
2 10/1/04 3:04 PM
13 10/1/04 4:40 PM
2 10/1/04 4:44 PM
13 10/1/04 4:58 PM
2 10/4/04 9:42 AM
13 10/4/04 3:23 PM
16 10/4/04 3:23 PM
2 10/4/04 3:23 PM
2 10/5/04 4:56 PM
2 10/6/04 9:35 AM
13 10/6/04 11:52 AM
13 10/6/04 12:47 PM
16 10/6/04 12:49 PM
2 10/6/04 12:49 PM
2 10/6/04 1:32 PM
13 10/6/04 4:06 PM
2 10/7/04 8:51 AM
2 10/7/04 11:39 AM
13 10/7/04 11:49 AM
"Alejandro Mesa" wrote:
> Can you post some ddl, sample data and expected result?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
>
> "Steve B" wrote:
>|||Thanks very much. I'm sure this is what I need. Not quite clear though on th
e
Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't match
.
I have added some example data to Alejandro's post.
Steve
"CBretana" wrote:
> Only if there is some field, IN THE TABLE, that allows you to "determine"
row
> sequence. I mean if there exists a ccolun, with unique values, which when
> sorted, will sequence the rows in the order in which "immediately followi
ng"
> has the meaning you want it to have. If that's so, let's say that column
is
> named <LogDate>.
> You have to join the table to itself, where, for each record in the first
> instance of the table is "Joined" to it's Immediate Follower, by "Joining"
> based on the value of LogDate being equal to the Minimum value of all the
> records with LogDate > Than this records LogDate...
> Select Prev.*, Next.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > First.LogDate)
> Join AUDIT_LOG Last On
> Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > Mid.LogDate)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
>
>
> "Steve B" wrote:
>|||Sorry they were table aliases in my first incantation, and neglected to
change them... They should be First, Mid, and Last
The SQL should be
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Using your actual column name,
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > First.AL_DATETIME)
Join AUDIT_LOG Last On
Last.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > Mid.AL_DATETIME)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Thank once again for your help. It works like a charm
Steve
"CBretana" wrote:
> Using your actual column name,
> Select First.*, Mid.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > First.AL_DATETIME)
> Join AUDIT_LOG Last On
> Last.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > Mid.AL_DATETIME)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
> "Steve B" wrote:
>|||Yr very welcome!
"Steve B" wrote:
> Thank once again for your help. It works like a charm
> Steve
> "CBretana" wrote:
>
Sunday, March 25, 2012
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech Znaniecki
Try checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>
|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
[vbcol=seagreen]
collumns[vbcol=seagreen]
|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>
|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech Znaniecki
Try checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>
|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
[vbcol=seagreen]collumns[vbcol=seagreen]
|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>
|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL :)
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
--
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL :)
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
--
Best Regards,
Wojciech Znaniecki
U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa³ w wiadomo¶ci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
> > Hello,
> > How can I check if backup log fiile is first log backup after database
> > backup ?
> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
> > I want to be sure that noone have removed the fist log file. using T-SQL
:)
> >
> > To create order in backkup log files i'm using FistLSN and LastLSN
collumns
> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> > database backup.
> > Any ideas ?
> >
> > --
> > Best Regards
> > Wojciech Znaniecki
> >
> >
> >|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa³ w wiadomo¶ci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>> Try checking table backupset in database msdb. Something like:
>> select
>> backup_set_id,
>> type,
>> backup_start_date,
>> backup_finish_date
>> from
>> msdb..backupset
>> where
>> database_name = 'your_db'
>> and type = 'L'
>> and backup_start_date > (select top 1 backup_finish_date from
>> msdb..backupset where database_name = 'your_db' and type = 'D' order by
>> backup_start_date desc)
>> order by
>> backup_start_date;
>>
>> AMB
>> "Wojtek Z" wrote:
>> > Hello,
>> > How can I check if backup log fiile is first log backup after database
>> > backup ?
>> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
>> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
>> > I want to be sure that noone have removed the fist log file. using T-SQL
> :)
>> >
>> > To create order in backkup log files i'm using FistLSN and LastLSN
> collumns
>> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
>> > database backup.
>> > Any ideas ?
>> >
>> > --
>> > Best Regards
>> > Wojciech Znaniecki
>> >
>> >
>> >
>|||U¿ytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa³ w wiadomo¶ci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
--
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL :)
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
--
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL :)
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
--
Best Regards,
Wojciech Znaniecki
U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa³ w wiadomo¶ci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
> > Hello,
> > How can I check if backup log fiile is first log backup after database
> > backup ?
> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
> > I want to be sure that noone have removed the fist log file. using T-SQL
:)
> >
> > To create order in backkup log files i'm using FistLSN and LastLSN
collumns
> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> > database backup.
> > Any ideas ?
> >
> > --
> > Best Regards
> > Wojciech Znaniecki
> >
> >
> >|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa³ w wiadomo¶ci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>> Try checking table backupset in database msdb. Something like:
>> select
>> backup_set_id,
>> type,
>> backup_start_date,
>> backup_finish_date
>> from
>> msdb..backupset
>> where
>> database_name = 'your_db'
>> and type = 'L'
>> and backup_start_date > (select top 1 backup_finish_date from
>> msdb..backupset where database_name = 'your_db' and type = 'D' order by
>> backup_start_date desc)
>> order by
>> backup_start_date;
>>
>> AMB
>> "Wojtek Z" wrote:
>> > Hello,
>> > How can I check if backup log fiile is first log backup after database
>> > backup ?
>> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
>> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
>> > I want to be sure that noone have removed the fist log file. using T-SQL
> :)
>> >
>> > To create order in backkup log files i'm using FistLSN and LastLSN
> collumns
>> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
>> > database backup.
>> > Any ideas ?
>> >
>> > --
>> > Best Regards
>> > Wojciech Znaniecki
>> >
>> >
>> >
>|||U¿ytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa³ w wiadomo¶ci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
--
Best Regards,
Wojciech Znaniecki
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumn
s
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
>
[vbcol=seagreen]
collumns[vbcol=seagreen]|||My guess is that the first log backup's FirstLsn need to be prior to the dat
abase backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...[vb
col=seagreen]
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>[/vbcol]|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumn
s
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
>
[vbcol=seagreen] collumns[vbcol=seagreen]|||My guess is that the first log backup's FirstLsn need to be prior to the dat
abase backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...[vb
col=seagreen]
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>[/vbcol]|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
Wednesday, March 21, 2012
detect log file size
Hello,
Is there a SQL statement I can use to get the current size of a database's
log file?
Thanks,
DaveEXEC sp_helpfile '<database>_log'
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"David Reynolds" <david_m_reynolds@.hotmail.com> wrote in message
news:9JeQa.21491$lJd1.15720@.news01.bloor.is.net.cable.rogers.com...
> Hello,
> Is there a SQL statement I can use to get the current size of a database's
> log file?
> Thanks,
> Dave
>|||This is a good one too, it gives you the sizes and percentage used for
the log files.
dbcc sqlperf('logspace')
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Is there a SQL statement I can use to get the current size of a database's
log file?
Thanks,
DaveEXEC sp_helpfile '<database>_log'
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"David Reynolds" <david_m_reynolds@.hotmail.com> wrote in message
news:9JeQa.21491$lJd1.15720@.news01.bloor.is.net.cable.rogers.com...
> Hello,
> Is there a SQL statement I can use to get the current size of a database's
> log file?
> Thanks,
> Dave
>|||This is a good one too, it gives you the sizes and percentage used for
the log files.
dbcc sqlperf('logspace')
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Monday, March 19, 2012
Detailled error description in a script component (data flow)
Hi,
I'm pretty new in SSIS and i have some problems with error log. I want to get detailled error description in a script component of a dataflow. for the moment I use thooses lines
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
and for unique constraints on a sql table I have this error : The data value violates integrity constraints.
For the same error, if i use an event handler on error, i have more row and the first of them is more explicit (Variable System::ErrorDescription)
An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert duplicate key row in object 'dbo.dimDepot' with unique index 'IX_dimDepot'.".
Is that possible to have a so detailled error text in a script componnent of a data flow? If yes, How?
Or if i use error event how can authorize the dataflow go ahead even if there is error.
thanks for you help
krest
Having written on the inadequate specificity of OLEDB error messaging available to the data flow as compared to that available to the OnError handler from the underlying SQL Native client, the following route was chosen to circumvent the problem.
Use the ADO.NET destination available in the SSIS Integration Services book written by Kirk Haselden.
Two modifications to the provided ADO.NET destination component in the book were made, to do exactly what you're looking for: inject detail error descriptions/codes as columns in the error output, to allow for their use when error row redirection is desired. They are as follows:
1. With the ADO.NET destination component, add two columns to the error output, NativeErrorMessage (type DT_WSTR ) and NativeErrorCode (type DT_I4).
2. Modify the error handler to populate the NativeErrorMessage and the NativeErrorCode from the the SqlException object.
I would post the code, but I'm not certain if that's allowed by the author or not.
Detailed Log information
We just started using SQL Server. I'm new on the SQL side of things. We have SQL setup in a test environment with users connecting and performing job related functions. An in house programmer has developed software for our users which connects to the SQL Server
I'm looking for information pertaining to events that have teken place within SQL. Does SQL give you details on updates and changes made to specific tables. I'm looking for some way of looking up item numbers and the user that entered the data. We have noticed that some of the users may be entering in wrong data within certain tables. And would like to educate them on what they are doing wrong.
I need to know what certain users are logging and entering into our SQL Server.
What are the most detailed logs that SQL Server provides that has information on what the users are doing has far an entering in data.Use triggers - they could help you a lot. But it needs to have some additional tables where you could save information about who did something and when.
Former Kentacky dba.|||triggers are certainly a way of auditing who doing what when.
a commercial product 'Encarta' sold by Lumigent is good for the purpose.
If users can enter wrong data, I would try hard to see if data model and design need to be modifed to enforce policies, such as adding a default, a check, a rule, etc. to prevent user errors.
Richard
Originally posted by snail
Use triggers - they could help you a lot. But it needs to have some additional tables where you could save information about who did something and when.
Former Kentacky dba.|||Check out the SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp). It can bury you in information about who is doing what, when, and how! Be forewarned, that you can take the "whole load" as a starting point, but that you'll HAVE to implement some filtering pretty quickly, or you'll drown in data!
-PatP
I'm looking for information pertaining to events that have teken place within SQL. Does SQL give you details on updates and changes made to specific tables. I'm looking for some way of looking up item numbers and the user that entered the data. We have noticed that some of the users may be entering in wrong data within certain tables. And would like to educate them on what they are doing wrong.
I need to know what certain users are logging and entering into our SQL Server.
What are the most detailed logs that SQL Server provides that has information on what the users are doing has far an entering in data.Use triggers - they could help you a lot. But it needs to have some additional tables where you could save information about who did something and when.
Former Kentacky dba.|||triggers are certainly a way of auditing who doing what when.
a commercial product 'Encarta' sold by Lumigent is good for the purpose.
If users can enter wrong data, I would try hard to see if data model and design need to be modifed to enforce policies, such as adding a default, a check, a rule, etc. to prevent user errors.
Richard
Originally posted by snail
Use triggers - they could help you a lot. But it needs to have some additional tables where you could save information about who did something and when.
Former Kentacky dba.|||Check out the SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp). It can bury you in information about who is doing what, when, and how! Be forewarned, that you can take the "whole load" as a starting point, but that you'll HAVE to implement some filtering pretty quickly, or you'll drown in data!
-PatP
Detailed error reporting. How to?
The best I can get out of my detail log is this which is no help. How do I find out what really happened. My windows app log is no help either
Date 5/24/2006 9:51:41 PM
Log Job History (DTS_xx)
Step ID 1
Server xx
Job Name DTS_xx
Step Name DTS_xx
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: xx\administrator. The package execution failed. The step failed.
http://support.microsoft.com/kb/918760|||
Michael - I'm glad your online right now. All that I'm trying to do is run stored procs on my database. My ssis package uses the correct login but I keep getting the error I posted. This was running fine on my 2000 box.
So I tried a few other things. I recreated the package on my production server and it works. Seems to be the problem is when I move it from my dev box to production its loosing something.
Sunday, March 11, 2012
Detaching and transaction logs
Hi,
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim Beyhan
As sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/Art...37/14337.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>
|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','truncateon ly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>
|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/Art...37/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db
>
|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','truncateon ly')
> Ensure that no open transactions are in the database. Check this using
DBCC[vbcol=seagreen]
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db
>
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim Beyhan
As sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/Art...37/14337.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>
|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','truncateon ly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>
|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/Art...37/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db
>
|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','truncateon ly')
> Ensure that no open transactions are in the database. Check this using
DBCC[vbcol=seagreen]
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db
>
Detaching and transaction logs
Hi,
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim BeyhanAs sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/ArticleID/14337/14337.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','truncateonly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/ArticleID/14337/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> > I tried everything to shrink the log files but no succeed. I read
> somewhere
> > that detaching the database, deleting the log file and then reattaching
is
> > the solution.
> > Can I be sure that all the data that was in the log file is now in the
db
> > with this process?
> >
> > Thanks in advance,
> >
> > Haim Beyhan
> >
> >
>|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','truncateonly')
> Ensure that no open transactions are in the database. Check this using
DBCC
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> > I tried everything to shrink the log files but no succeed. I read
> somewhere
> > that detaching the database, deleting the log file and then reattaching
is
> > the solution.
> > Can I be sure that all the data that was in the log file is now in the
db
> > with this process?
> >
> > Thanks in advance,
> >
> > Haim Beyhan
> >
> >
>
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim BeyhanAs sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/ArticleID/14337/14337.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','truncateonly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/ArticleID/14337/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> > I tried everything to shrink the log files but no succeed. I read
> somewhere
> > that detaching the database, deleting the log file and then reattaching
is
> > the solution.
> > Can I be sure that all the data that was in the log file is now in the
db
> > with this process?
> >
> > Thanks in advance,
> >
> > Haim Beyhan
> >
> >
>|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','truncateonly')
> Ensure that no open transactions are in the database. Check this using
DBCC
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> > I tried everything to shrink the log files but no succeed. I read
> somewhere
> > that detaching the database, deleting the log file and then reattaching
is
> > the solution.
> > Can I be sure that all the data that was in the log file is now in the
db
> > with this process?
> >
> > Thanks in advance,
> >
> > Haim Beyhan
> >
> >
>
Detaching and transaction logs
Hi,
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim BeyhanAs sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/Ar...337/14337.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','
truncateonly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/Ar...337/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db[vbcol=seagreen]
>|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','
truncateonly')
> Ensure that no open transactions are in the database. Check this using
DBCC
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db[vbcol=seagreen]
>
I'm using SQL 7 with database 1 GB and transaction log 4 GB.
I tried everything to shrink the log files but no succeed. I read somewhere
that detaching the database, deleting the log file and then reattaching is
the solution.
Can I be sure that all the data that was in the log file is now in the db
with this process?
Thanks in advance,
Haim BeyhanAs sp_detach_db issues Checkoint command first, this should be safe. But
befor doing this, check the article at
http://www.winnetmag.com/Article/Ar...337/14337.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Hi,
Before detaching, can you try this:-
1. Backup the transaction log
Backup log <dbname> to disk='c:\backup\dbname.trn'
2. DBCCSHRINKFILE('logical_log_file_name','
truncateonly')
Ensure that no open transactions are in the database. Check this using DBCC
OPENTRAN('dbname')
If this is not working out, go with Dejan Sarka's suggestion.
Thanks
Hari
MCDBA
"Haim Beyhan" <haimb@.enigma.com> wrote in message
news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm using SQL 7 with database 1 GB and transaction log 4 GB.
> I tried everything to shrink the log files but no succeed. I read
somewhere
> that detaching the database, deleting the log file and then reattaching is
> the solution.
> Can I be sure that all the data that was in the log file is now in the db
> with this process?
> Thanks in advance,
> Haim Beyhan
>|||Thanks for the reply.
Haim
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23bYwx2CLEHA.3880@.TK2MSFTNGP09.phx.gbl...
> As sp_detach_db issues Checkoint command first, this should be safe. But
> befor doing this, check the article at
> http://www.winnetmag.com/Article/Ar...337/14337.html.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db[vbcol=seagreen]
>|||Thank you for the reply.
I tried your steps and it did not help. then I tried Dejan's suggestion.
The server created a new log file after the database is reattached.
I hope everything is on the db.
Haim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23v4%23dNDLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Before detaching, can you try this:-
> 1. Backup the transaction log
> Backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. DBCCSHRINKFILE('logical_log_file_name','
truncateonly')
> Ensure that no open transactions are in the database. Check this using
DBCC
> OPENTRAN('dbname')
> If this is not working out, go with Dejan Sarka's suggestion.
> Thanks
> Hari
> MCDBA
>
> "Haim Beyhan" <haimb@.enigma.com> wrote in message
> news:uRU6eyCLEHA.4080@.TK2MSFTNGP12.phx.gbl...
> somewhere
is[vbcol=seagreen]
db[vbcol=seagreen]
>
Detaching and Attaching DB;s
I'm trying to detach a DB and then attach it with the
data and log files in a different location.
Here's what I'm doing.
I detach the DB in question
Copy the data and log files to a different location
Attach DB, using the copied files.
It attaches fine, but it makes the DB Read-Only, and I'm
unable to uncheck the read-only option in the properties
of the db. It gives a long error about the possibiliy of
the data and log files not being correct, which I know
they are.
Any thoughts?
Thanks much
MattSuggest running DBCC CHECKDB on the source, if good try detaching again
--
HTH
Ryan Waight, MCDBA, MCSE
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||Hi Matt
Is it possible the files themselves are read only in the OS? Maybe the
folder you copied them to had its readonly property set, and the db files
inherited that.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||thanks for the response. I did check that as well and
that is not the case either.
matt
>--Original Message--
>Hi Matt
>Is it possible the files themselves are read only in the
OS? Maybe the
>folder you copied them to had its readonly property set,
and the db files
>inherited that.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Matt Bender" <matt.bender@.berbee.com> wrote in message
>news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
>> I'm trying to detach a DB and then attach it with the
>> data and log files in a different location.
>> Here's what I'm doing.
>> I detach the DB in question
>> Copy the data and log files to a different location
>> Attach DB, using the copied files.
>> It attaches fine, but it makes the DB Read-Only, and
I'm
>> unable to uncheck the read-only option in the
properties
>> of the db. It gives a long error about the possibiliy
of
>> the data and log files not being correct, which I know
>> they are.
>> Any thoughts?
>> Thanks much
>> Matt
>
>.
>|||Are you sure no one is in the database? What happens when you try to set the
db to not read-only using ALTER DATABASE WITH ROLLBACK...
(Please see BOL for full syntax details.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <anonymous@.discussions.microsoft.com> wrote in message
news:06d601c3b2cd$4df5cca0$a101280a@.phx.gbl...
> thanks for the response. I did check that as well and
> that is not the case either.
> matt
> >--Original Message--
> >Hi Matt
> >
> >Is it possible the files themselves are read only in the
> OS? Maybe the
> >folder you copied them to had its readonly property set,
> and the db files
> >inherited that.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Matt Bender" <matt.bender@.berbee.com> wrote in message
> >news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> >> I'm trying to detach a DB and then attach it with the
> >> data and log files in a different location.
> >>
> >> Here's what I'm doing.
> >> I detach the DB in question
> >> Copy the data and log files to a different location
> >> Attach DB, using the copied files.
> >> It attaches fine, but it makes the DB Read-Only, and
> I'm
> >> unable to uncheck the read-only option in the
> properties
> >> of the db. It gives a long error about the possibiliy
> of
> >> the data and log files not being correct, which I know
> >> they are.
> >>
> >> Any thoughts?
> >>
> >> Thanks much
> >> Matt
> >
> >
> >.
> >
data and log files in a different location.
Here's what I'm doing.
I detach the DB in question
Copy the data and log files to a different location
Attach DB, using the copied files.
It attaches fine, but it makes the DB Read-Only, and I'm
unable to uncheck the read-only option in the properties
of the db. It gives a long error about the possibiliy of
the data and log files not being correct, which I know
they are.
Any thoughts?
Thanks much
MattSuggest running DBCC CHECKDB on the source, if good try detaching again
--
HTH
Ryan Waight, MCDBA, MCSE
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||Hi Matt
Is it possible the files themselves are read only in the OS? Maybe the
folder you copied them to had its readonly property set, and the db files
inherited that.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||thanks for the response. I did check that as well and
that is not the case either.
matt
>--Original Message--
>Hi Matt
>Is it possible the files themselves are read only in the
OS? Maybe the
>folder you copied them to had its readonly property set,
and the db files
>inherited that.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Matt Bender" <matt.bender@.berbee.com> wrote in message
>news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
>> I'm trying to detach a DB and then attach it with the
>> data and log files in a different location.
>> Here's what I'm doing.
>> I detach the DB in question
>> Copy the data and log files to a different location
>> Attach DB, using the copied files.
>> It attaches fine, but it makes the DB Read-Only, and
I'm
>> unable to uncheck the read-only option in the
properties
>> of the db. It gives a long error about the possibiliy
of
>> the data and log files not being correct, which I know
>> they are.
>> Any thoughts?
>> Thanks much
>> Matt
>
>.
>|||Are you sure no one is in the database? What happens when you try to set the
db to not read-only using ALTER DATABASE WITH ROLLBACK...
(Please see BOL for full syntax details.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <anonymous@.discussions.microsoft.com> wrote in message
news:06d601c3b2cd$4df5cca0$a101280a@.phx.gbl...
> thanks for the response. I did check that as well and
> that is not the case either.
> matt
> >--Original Message--
> >Hi Matt
> >
> >Is it possible the files themselves are read only in the
> OS? Maybe the
> >folder you copied them to had its readonly property set,
> and the db files
> >inherited that.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Matt Bender" <matt.bender@.berbee.com> wrote in message
> >news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> >> I'm trying to detach a DB and then attach it with the
> >> data and log files in a different location.
> >>
> >> Here's what I'm doing.
> >> I detach the DB in question
> >> Copy the data and log files to a different location
> >> Attach DB, using the copied files.
> >> It attaches fine, but it makes the DB Read-Only, and
> I'm
> >> unable to uncheck the read-only option in the
> properties
> >> of the db. It gives a long error about the possibiliy
> of
> >> the data and log files not being correct, which I know
> >> they are.
> >>
> >> Any thoughts?
> >>
> >> Thanks much
> >> Matt
> >
> >
> >.
> >
Detached model and msdb - sql server stopped!
Hi,
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>
detached db cant recover
A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log?
Any ideas?
PLEASE HELP!
Richard
Hi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on?
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Take a look into the below KB article.
http://support.microsoft.com/default...b;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on?
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error?
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error?
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log?
Any ideas?
PLEASE HELP!
Richard
Hi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on?
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Take a look into the below KB article.
http://support.microsoft.com/default...b;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on?
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error?
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error?
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
detached db cant recover
A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset
of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in data
base
[envision] (7). The OS file handle is 0x0000055C. The offset of the la
test
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/defaul...kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF]
in
> database [envision] (7). The OS file handle is 0x0000055C. The offse
t of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery i
t
> crashes with a primary key violation at abuot 9% complete. I am starting
to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset
of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in data
base
[envision] (7). The OS file handle is 0x0000055C. The offset of the la
test
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/defaul...kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF]
in
> database [envision] (7). The OS file handle is 0x0000055C. The offse
t of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery i
t
> crashes with a primary key violation at abuot 9% complete. I am starting
to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
detached db cant recover
A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
--
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>>A genius here at work decided to detach a database because the disk the
>>log file was on was full. He copied the log to a different drive and now
>>we cant reattach the db. The db is 260 gig, the log us 70 gig. SQL
>>Server looks like it does the analysis to reattach in about 5 minutes then
>>starts what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>> A genius here at work decided to detach a database because the disk the log
>> file was on was full. He copied the log to a different drive and now we
>> cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>> looks like it does the analysis to reattach in about 5 minutes then starts
>> what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
--
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>>A genius here at work decided to detach a database because the disk the
>>log file was on was full. He copied the log to a different drive and now
>>we cant reattach the db. The db is 260 gig, the log us 70 gig. SQL
>>Server looks like it does the analysis to reattach in about 5 minutes then
>>starts what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>> A genius here at work decided to detach a database because the disk the log
>> file was on was full. He copied the log to a different drive and now we
>> cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>> looks like it does the analysis to reattach in about 5 minutes then starts
>> what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Subscribe to:
Posts (Atom)