DataSources

I have come into a company where my predesessor used a TON of differently
named RDS files to connect to the same SQL Server Database. My suspicion is
that he thought that datasources were the same as datasets and created one
for each.
Is there any good reason to do this?
Should they all have been changed to be something generic like
"ConnectTo{servername}Production.RDS (instead of {Report Name}.RDS)
Any thoughts?
Thanks,
KeithIm sure there are different schools of thought, but I enjoy using all the
same name and have never had reason to make them all different, nor have I
had any issues.
Michael C
"greenmtnsun" wrote:
> I have come into a company where my predesessor used a TON of differently
> named RDS files to connect to the same SQL Server Database. My suspicion is
> that he thought that datasources were the same as datasets and created one
> for each.
> Is there any good reason to do this?
> Should they all have been changed to be something generic like
> "ConnectTo{servername}Production.RDS (instead of {Report Name}.RDS)
> Any thoughts?
> Thanks,
> Keith
>
>sql

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

DataFlow suggestion

I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.

Here is what i do.

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

please help...

Not sure without looking at your data, but look into using the conditional split transformation to perform your logic checks. Use a derived column transformation to add a column.|||

sureshv wrote:

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

sureshv wrote:

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

Use a derived column to add extra columns to data flow pipeline

sureshv wrote:

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

There are tons of threads discussing that issue; the most popular is to use Lookup transform against the destination table; if a match occurs means the row already exists; otherwise it does not.

|||

In the first case.

Data is just a text file with column names in the first row.

eg:

name address telno

xxx yyy zzz

xxx sdfdsf sdfsf

But i dont want the column names coming from text file. instead i want to have my own column names(which i can and know to create)

If i transter data as in the text file the column names would appear in the first row.just to get rid of it i want to remove column name from text file.

In second case:

I use derived column to add a column,and conditional split for bad data.

My point is in the new column how should i insert the status(insert) or )update.

what should i use.

|||

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

I dont want to use this i can have and want to have my own column names.WHich can be done and i know about it.

please look at my reply earlier

|||

sureshv wrote:

My point is in the new column how should i insert the status(insert) or )update.

see if this helps:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

you could add a derived column transform at after each lookup transform and add there a column with the expected value(insert/update)...

Datafiles placement in filegroup

I'm working with a production database connecting remotely. All the datafile
s, index files and log is placed on the same disk but in different filegroup
names based on separate physical files. But I expect if I place the index f
iles or/add log files sep
arate from the datafiles I mean in different disk it will improve the perfor
mance.
If my expectation is correct then can I change the place of the index and lo
g files in separate location while the database is on-line yes the database
is implemented with log-shipping too.
Please evaluate my query and do give a proper suggestion.
Thanks in advance
Sunilsurely it will improve the performance if the log files are placed seperate
from the data files (different disks).
for the indexes u can delete the existing index and recreate it specifying
the new location.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4690C174-4988-4737-82D9-7C0ED08FDF5D@.microsoft.com...

quote:

---

> I'm working with a production database connecting remotely. All the

---

datafiles, index files and log is placed on the same disk but in different
filegroup names based on separate physical files. But I expect if I place
the index files or/add log files separate from the datafiles I mean in
different disk it will improve the performance.

quote:

---

> If my expectation is correct then can I change the place of the index and

---

log files in separate location while the database is on-line yes the
database is implemented with log-shipping too.

quote:

---

>
> Please evaluate my query and do give a proper suggestion.
>
> Thanks in advance
> Sunil
>

---

Datafiles placement in filegroup

I'm working with a production database connecting remotely. All the datafiles, index files and log is placed on the same disk but in different filegroup names based on separate physical files. But I expect if I place the index files or/add log files separate from the datafiles I mean in different disk it will improve the performance.
If my expectation is correct then can I change the place of the index and log files in separate location while the database is on-line yes the database is implemented with log-shipping too.
Please evaluate my query and do give a proper suggestion.
Thanks in advance
Sunilsurely it will improve the performance if the log files are placed seperate
from the data files (different disks).
for the indexes u can delete the existing index and recreate it specifying
the new location.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4690C174-4988-4737-82D9-7C0ED08FDF5D@.microsoft.com...
> I'm working with a production database connecting remotely. All the
datafiles, index files and log is placed on the same disk but in different
filegroup names based on separate physical files. But I expect if I place
the index files or/add log files separate from the datafiles I mean in
different disk it will improve the performance.
> If my expectation is correct then can I change the place of the index and
log files in separate location while the database is on-line yes the
database is implemented with log-shipping too.
>
> Please evaluate my query and do give a proper suggestion.
>
> Thanks in advance
> Sunil
>|||You are mostly correct.
Creating new files doesn't automatically improve
performace, it also depends upon the raid configuration of
the disk its going on.
For instance putting a log file on a RAID 0 disk will make
it faster than putting it on a raid 5 disk.
Although its different for every type of application a
normal implementation with cost constraints its to put the
log files on a raid 0 or raid 0+1 with the data files on
raid 5.
J
>--Original Message--
>I'm working with a production database connecting
remotely. All the datafiles, index files and log is placed
on the same disk but in different filegroup names based on
separate physical files. But I expect if I place the index
files or/add log files separate from the datafiles I
mean in different disk it will improve the performance.
>If my expectation is correct then can I change the place
of the index and log files in separate location while the
database is on-line yes the database is implemented with
log-shipping too.
>
>Please evaluate my query and do give a proper suggestion.
>
>Thanks in advance
>Sunil
>.
>

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 data
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks :)"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
--
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks :)|||Hi,
I have the same problem, but i´m not a dba expert. How is sintaxe to create
a new table and accurate the it´s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:
> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
> >I am having trouble moving BLOBs (text fields).
> >
> > I have a 200Gb database in a single data file and want to split it into 4
> > even sized files (I have my reasons). To achieve this I plan on adding 3
> > data
> > files to the Primary filegroup. From there I will issue a DBCC dbreindex
> > on
> > all my tables, which effectively recreates the index (or table if its a
> > clustered index) across all datafiles and I get my even distribution.
> >
> > My plan comes unstuck when I encounter text fields and other BLOBs (as the
> > clustered index just contains pointers doesnt it'). How can I move them?
> > Do
> > I have to do a select into or bcp? Can I just issue a shrink file on the
> > oringal large file or does that just push pages from the end of that data
> > file to the beginning and not to other datafiles'
> >
> > Any suggestions would be great, I don't really want to create another
> > filegroup just yet.
> >
> > Thanks :)
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...
> Hi,
> I have the same problem, but i´m not a dba expert. How is sintaxe to
> create
> a new table and accurate the it´s will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>> You will need to create a new filegroup so you can create a new table on
>> this filegroup. Copy the old data to the new table. Drop the old table.
>> Rename the new tb to the old name. And then shrink the db to reclaim the
>> space.
>> --
>> -oj
>>
>> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
>> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>> >I am having trouble moving BLOBs (text fields).
>> >
>> > I have a 200Gb database in a single data file and want to split it into
>> > 4
>> > even sized files (I have my reasons). To achieve this I plan on adding
>> > 3
>> > data
>> > files to the Primary filegroup. From there I will issue a DBCC
>> > dbreindex
>> > on
>> > all my tables, which effectively recreates the index (or table if its a
>> > clustered index) across all datafiles and I get my even distribution.
>> >
>> > My plan comes unstuck when I encounter text fields and other BLOBs (as
>> > the
>> > clustered index just contains pointers doesnt it'). How can I move
>> > them?
>> > Do
>> > I have to do a select into or bcp? Can I just issue a shrink file on
>> > the
>> > oringal large file or does that just push pages from the end of that
>> > data
>> > file to the beginning and not to other datafiles'
>> >
>> > Any suggestions would be great, I don't really want to create another
>> > filegroup just yet.
>> >
>> > Thanks :)
>>

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 dat
a
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks |||Hi,
I have the same problem, but i′m not a dba expert. How is sintaxe to create
a new table and accurate the it′s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:

> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I have the same problem, but im not a dba expert. How is sintaxe to
> create
> a new table and accurate the its will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>

Datafile limitations per database

How many mdf files can you use in comprising one database. I know one can
setup primary & secondary but how far down the list can it go? Can you have
30 files (each limited to 50GB)?You are well within the limits of SQL Server. One database can have over 32,
000 files 4 TB each.
You can look-up 'maximum capacity specifications' in BOL.
DeeJay
"kisstene" wrote:

> How many mdf files can you use in comprising one database. I know one can
> setup primary & secondary but how far down the list can it go? Can you ha
ve
> 30 files (each limited to 50GB)?

Datafile limitations per database

How many mdf files can you use in comprising one database. I know one can
setup primary & secondary but how far down the list can it go? Can you have
30 files (each limited to 50GB)?You are well within the limits of SQL Server. One database can have over 32,
000 files 4 TB each.
You can look-up 'maximum capacity specifications' in BOL.
DeeJay
"kisstene" wrote:
> How many mdf files can you use in comprising one database. I know one can
> setup primary & secondary but how far down the list can it go? Can you have
> 30 files (each limited to 50GB)?

DataDir Property - Is it possible to store files on more than 1 drive?

The "DataDir" property for an AS 2005 instance seems to only allow for one path to be specified. I am working with a very large data set that is being updated frequently and I would like to be able to reference more than on LUN on my disk array.

Yes, you can.

Take a look at the StorageLocation property of your partition.

Edward Melomed (MSFT)

--

This posting is provided "AS IS" with no warranties, and confers no rights.

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James ColbertSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx
.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
---
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

Databases missing from Backup Task of Maintenance Plan

I'm moving databases from SQL7 to another server with SQL2005.

I have created new databases in SQL2005 via a restore from backup files created in the old server. Now I want to back up these new databases but they don't show in the Backup Task of a new or existing Maintenance Plan in Management Studio. I can backup them manually though. Any ideas?

I assume you have restored this database to a local 2005 server. If this is the case, they should show up. Maintenance plans can not make connections to SQL 7. Can you see this database via Management Studio?|||

Rob,

Yes, that's correct. I restored the databases to another server running SQL2005. None of the databases show up in any task of Management Studio's Maintenance Plans (eg Backup, Shrink, ReOrg)

-Bill

|||

Found the problem - the compatibility level was set to SQL Server 7.0 by default ( under database properties->options). As soon as I changed the compatiblity level to SQL Server 2000, the databases showed up in the Maintenance Plan tasks.

Thank you for your responses.

Databases in read-only

We have a client with several databases in read-only mode. When we try and take the dbs out of read-only, we get the path to the mdf files may be incorrect, which they are not. SQL 2000, SP3a. Has anybody seen this? The files themselves are not read-only, I gave the network services account full control and still nothing. The issue started when the client lost power, lasst week, the dbs came up suspect. Detach/Attach brought them back up in single user.

Any hep is appreciated.

What was the exact sequence of events after the power failure last week? What were the error log errors when the databases came back as suspect? Why did you/they attempt to detach/attach?

Can you post the T-SQL you're using, and the errors you get back?

Thanks

|||It turned out to be a permissions issue. The account used to start sql server didn't have the correct permissions on the data/log/mssql directories. They had changed domains and I don't think they new exactly what they were doing. I suspect the account was a domain admin before the domain change.

I used detach/attach because sp_resetstatus didn't take them out of suspect mode.

Thanks

Database/Log placement

I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...

quote:

---

> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave

---

Database/Log placement

I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave

database with two data files

I only need short description or even link about database with two data
files and/or two log files. How does it work,...?
thanks
Hi
In BOL, look up the keyword "filegroups, described". Gives a perfect
description and examples.
Regards
Mike
"Lugos" wrote:

> I only need short description or even link about database with two data
> files and/or two log files. How does it work,...?
> thanks
>
|||Thanks. That's what I am looking for. Shame is i did not find it myself.
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> In BOL, look up the keyword "filegroups, described". Gives a perfect
> description and examples.
> Regards
> Mike
> "Lugos" wrote:
>

database with two data files

I only need short description or even link about database with two data
files and/or two log files. How does it work,...?
thanksHi
In BOL, look up the keyword "filegroups, described". Gives a perfect
description and examples.
Regards
Mike
"Lugos" wrote:
> I only need short description or even link about database with two data
> files and/or two log files. How does it work,...?
> thanks
>|||Thanks. That's what I am looking for. Shame is i did not find it myself.
Mike Epprecht (SQL MVP) wrote:
> Hi
> In BOL, look up the keyword "filegroups, described". Gives a perfect
> description and examples.
> Regards
> Mike
> "Lugos" wrote:
>
>>I only need short description or even link about database with two data
>>files and/or two log files. How does it work,...?
>>thanks