Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
Possible? yes.
Advisable? definitely NOT.
Supported? No.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||check kb below
http://support.microsoft.com/default...b;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default...b;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Thursday, March 29, 2012
Database Stored on NaS
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
Tuesday, March 27, 2012
Database Snapshot
Need some help, i have some database snapshots files provided from an external source. Need to be able to understand how i get them back into a database format if possible.
Files for example are table1.bcp, table2.bcp with als a file called scheme.sql which sets up these tables in sql but does not populate them. Nothing else was provided except a .vcd file which i dont know whtat its for.
any clues?
Dan
This link describes how to restore a database from a database snapshot
http://www.sqlservercentral.com/columnists/akhosla/2733.asp
unfortunately I don't have right now access to an SQL server to see how to 'make' SQL Server 'see' a snapshot created by another server
|||I dont have the original database though, only these files and this link assumes you have the original database?/
Dan
|||yes this link assumes you made the snapshot on the same sql server :(
|||
Are you CERTAIN that these files were created using the SQL Server Database Snapshot feature?
It looks to me as if you have a SQL script to create tables, and BCP data files to feed into the BCP (Bulk copy) utility to load the data.
I would read BOL on BCP and attempt to load the files that way.
|||Yes you are correct, this does indeed seem to be a Bulk Copy in Native format, guess i was going down the wrong path.
Thanks i now have the data in the correct format, also the extract works in SQL 2000 which is good.
Much appreciated.
Dan
Database sizing question
Hi,
I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.
ThanksHowdy
Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.
Try this ( its easier and faster ) :
(1) Create the table you are importing data to.
(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).
(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.
(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.
Cheers,
SG.
I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.
ThanksHowdy
Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.
Try this ( its easier and faster ) :
(1) Create the table you are importing data to.
(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).
(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.
(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.
Cheers,
SG.
Thursday, March 22, 2012
Database Size
In the table called system files, we have a field called size = Size of the file (in 8-KB pages). And if i look at the physical drive on the explorer this Size is different from the one in the database on system tables. Could someone tell me how to calculate them.
Secondly, i can see that the dumpfile is stored in sysdevices table, but where can i get the size of this dumpfile (.bak) because is always 0 after i dumped the file and again is not 0 on the physical drive of the explorerAbout your 2nd question:
The lines located on the system table only provide the physical location of a logical dump files. The size is 0 in any case.sql
Secondly, i can see that the dumpfile is stored in sysdevices table, but where can i get the size of this dumpfile (.bak) because is always 0 after i dumped the file and again is not 0 on the physical drive of the explorerAbout your 2nd question:
The lines located on the system table only provide the physical location of a logical dump files. The size is 0 in any case.sql
database size
Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many[vbcol=seagreen]
> daily.
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professionalsql
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many[vbcol=seagreen]
> daily.
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professionalsql
database size
Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
Wednesday, March 21, 2012
database size
Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql
Database server will not expand mdf or ndf files
Has anyone had an issue with SQL Server not being able to expand
against a RAID 5 file system? My current configuration is that the
server is started and stopped using the local system account. I have
only one database (besides the master, model,etc)on the server. What
has happend to me several times is that the primary database in
question try's to expand the main datafile for the database (.mdf). I
setup the database to not expand automatically initially so that I can
be sure that we have enough file system space. Becuase of problems with
the application I decided to automatically expand. The other day the
developers came to me indicating that the databse was full and needed
to be expanded. Knowing that the database was in automatic expanding
more I was surprise to hear this. I went into EM and attempted to
expand first the log and it would not indicating that it there was an
issue in attempting to do so. I have never heard of a database not
being able to expand. I ran DBCC's, etc and it came up clean. I tried
to back the database up to disk and it would not backup. I finally had
to rename the datbase and rebuild it using DTS and scripts. I thought
I had fixed it only to find out today that it (again) won't expand. I
renamed the datbase and then tried taking an older backup file and
restore it and it would not restore. This problem seems to be related
to the file system but how I do not know.
against a RAID 5 file system? My current configuration is that the
server is started and stopped using the local system account. I have
only one database (besides the master, model,etc)on the server. What
has happend to me several times is that the primary database in
question try's to expand the main datafile for the database (.mdf). I
setup the database to not expand automatically initially so that I can
be sure that we have enough file system space. Becuase of problems with
the application I decided to automatically expand. The other day the
developers came to me indicating that the databse was full and needed
to be expanded. Knowing that the database was in automatic expanding
more I was surprise to hear this. I went into EM and attempted to
expand first the log and it would not indicating that it there was an
issue in attempting to do so. I have never heard of a database not
being able to expand. I ran DBCC's, etc and it came up clean. I tried
to back the database up to disk and it would not backup. I finally had
to rename the datbase and rebuild it using DTS and scripts. I thought
I had fixed it only to find out today that it (again) won't expand. I
renamed the datbase and then tried taking an older backup file and
restore it and it would not restore. This problem seems to be related
to the file system but how I do not know.
So, I am ready to run rebuild master but I have sone this before only
to have this come back on me. I am at a complete loss. In the past I
have had to rebuild the entire server and database from scratch. The
only problem is that this has been done 3 times now with no complete
solution or explaination. If any of you have seen this type of
behavior and know whats going on please, please let me know what you
think the case and solution is!"2centbob" wrote:
> Has anyone had an issue with SQL Server not being able to expand
> against a RAID 5 file system? My current configuration is that the
> server is started and stopped using the local system account. I have
> only one database (besides the master, model,etc)on the server. What
> has happend to me several times is that the primary database in
> question try's to expand the main datafile for the database (.mdf). I
> setup the database to not expand automatically initially so that I can
> be sure that we have enough file system space. Becuase of problems with
> the application I decided to automatically expand. The other day the
> developers came to me indicating that the databse was full and needed
> to be expanded. Knowing that the database was in automatic expanding
> more I was surprise to hear this. I went into EM and attempted to
> expand first the log and it would not indicating that it there was an
> issue in attempting to do so. I have never heard of a database not
> being able to expand. I ran DBCC's, etc and it came up clean. I tried
> to back the database up to disk and it would not backup. I finally had
> to rename the datbase and rebuild it using DTS and scripts. I thought
> I had fixed it only to find out today that it (again) won't expand. I
> renamed the datbase and then tried taking an older backup file and
> restore it and it would not restore. This problem seems to be related
> to the file system but how I do not know.
<snip
I don't know of issues specifically with RAID 5 (unless your RAID card has
gone bonkers), but here's a few guesses (mostly based on my trying to figure
out why the file system or something else would stop a file from expanding).
- Are you sure you have enough disk space? (I'm pretty that's not it and you
would have seen it, but better safe than sorry.) One place to look is
programs that might create huge temp files that eventually go away: we had a
server that ran multiple concurrent server processes. We had a heck of a
time figuring out why disk space seemingly came and went in huge chunks
until we realized that 3rd party code in our services was creating *huge*
temp files (because a few programmers didn't code for users requesting
reports with 4 million lines before control breaks :).
- Is your file system NTFS or FAT? Not being able to expand and then not
being able to backup or restore sounds fishy: could you be bumping into
FAT's file size limit? If I recall it's 4GB in FAT32 and 2GB in earlier FAT
versions.
- Are disk quotas enabled on the server? I've never even touched these in
Windows, so I have no idea where you would look... For that matter, does
your RAID hw/sw combo allow for any kind of quota?
- I'm pretty sure you already have, but in case you haven't, have you
checked the SQL Server logs and the OS event logs?
Good Luck,
Craig|||Thanks for your reply. In these cases its allways novce to have a
complete picture and that doesn't necessarly get conveyed sometimes.
So, a little more information is warrented. The application that uses
the database is a Java app sitting on a different server. The database
server has no application running on it. The application was written by
a vendor. Thier requirements require that the datbase owner have full
rights to the database, i.e., using sp_changedbowner to that user. If I
did not use that approach then the application had problems upon
installation and therefore would not properly install. So, as I said I
changed it. Prior to this expereince the database was left to expand as
it needed and it did with no issues. The two circumstances that I
refered in my earlier email: the file system filled up and the database
could not expand. In addition, the server could not be reached and so
we had to shut it down hard. When it came back up we could not use the
database nor could we back it up. We were forced to rebuild the server:
OS and SQL Server. Later, a similar incident happened again and we were
forced (again) to rebuild. This last time, I had an additional 40 GB
added so we would not have a file system space problem again. I put the
database and log into a non-expansion mode so that when the application
would not accidently consume all of the disk space. However, the
database hit the high water mark on the datafile and could not expand.
I was notofied and of the problem and went to expand the file and it
would not expand again. No you most of the information.
At this point I am starting to think that as long the database has file
space to expand into and is not resitricted in any way the application
would probably work alright. However, because "sa" does not own the
database, the database owner probably needs "sa" rights. This is just
conjecture at this point. Funny thing, when this happend, the last
time, the "TaskPad" information came up with an error saying it could
not display the information and wanted to me to stop running the rest of
the script. I am concered that OS files are being walked on somehow.
Boy, I never had this expereince using Sybase and I have never seen
anyting like it in Oracle as well. But then again those were Unix
databases that I worked on, and not Windows.
Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowner to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.
A lots of words, but, frankly, not very much information.
First of all, who owns the database does not matter. Auto-grow will
work anyway.
Since you seem to have difficulties to explain what is going on, I would
like you to run sp_helpdb on your database and post the output. That
will at least give some minimum of information for us to work from.
I would also like you do a DIR on the disks where the data and log files
reside, and post the bottom lines from that output.
In your previous message you said that you could not backup the database,
but you never explained why. Did you get an error message? Or how did
you conclude that the backup wasn't working?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
Even though you mention EM! Are you using MSDE?
Do you have disk quotas?
Are you using mount points?
It may help if you posted the version
http://www.aspfaq.com/show.asp?id=2160.
John
"Bob Schmitz" <bschmitz4@.wi.rr.com> wrote in message
news:4204f360$1_2@.127.0.0.1...
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowner to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.
> At this point I am starting to think that as long the database has file
> space to expand into and is not resitricted in any way the application
> would probably work alright. However, because "sa" does not own the
> database, the database owner probably needs "sa" rights. This is just
> conjecture at this point. Funny thing, when this happend, the last
> time, the "TaskPad" information came up with an error saying it could
> not display the information and wanted to me to stop running the rest of
> the script. I am concered that OS files are being walked on somehow.
> Boy, I never had this expereince using Sybase and I have never seen
> anyting like it in Oracle as well. But then again those were Unix
> databases that I worked on, and not Windows.
> Thanks.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thats becuase this was a very difficult and weird situation. I knew
that I would not be able to explain it all and some would have
questions. sp_helpdb is not the problem becuase it shows the database.
There are no errors in the logs except when I try to backup or if i
tried to restore the database in question. When I ran a dir on the
filesystem the database files and there sizes show that they have
expanded but the databsae does no reflect this.
Now, what I have doen since then is to blow away the master, model,
msdb, and tempdb. I then ran the rebuild.exe program. That seems to
have fixed the problem as after I reattached the database I was able to
expand but log and data.
2centbob
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> Thats becuase this was a very difficult and weird situation. I knew
> that I would not be able to explain it all and some would have
> questions. sp_helpdb is not the problem becuase it shows the database.
> There are no errors in the logs except when I try to backup or if i
> tried to restore the database in question. When I ran a dir on the
> filesystem the database files and there sizes show that they have
> expanded but the databsae does no reflect this.
> Now, what I have doen since then is to blow away the master, model,
> msdb, and tempdb. I then ran the rebuild.exe program. That seems to
> have fixed the problem as after I reattached the database I was able to
> expand but log and data.
I strongly suspect that you put far more work into fix this that was
required.
However, since your choice is not to share the information I asked you
to, I'm afraid I can't help you with advice of what you should have done.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can suspect all you want ... Unless you had been there working side
by side you don't know anything. Not only that, I resent your attitude
as though you know more than anyone else on this site. Please, in the
future, if you dont have something say other than criticize someone,
please refrain from responding. I don't need it and suspect others
don't need it as well.
For others: The end users were screaming to have this system back and so
my time was limited in responding. THE ONLY THING THAT HAS WORKED HAS
BEEN TO REBUILD THE MASTER DATABASE. Be that as it may, it works now,
thanks to all that replied.
2centbob
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> You can suspect all you want ... Unless you had been there working side
> by side you don't know anything. Not only that, I resent your attitude
> as though you know more than anyone else on this site. Please, in the
> future, if you don't have something say other than criticize someone,
> please refrain from responding. I don't need it and suspect others
> don't need it as well.
You appeared to ask for help. And that's basically what I do here. Try
to help people. But often, I need more information about the case, so I
ask for that. It's true, that I have not been on your site, so I don't
know what happened. I have however been trying to find out, but you have
been very willing to give me the information that I have asked for. Of
course, you may do as you please. But you cannot really expect to get any
useful assistence that way.
And that is a piece of advice for the future when you have a need to
ask for help.
> For others: The end users were screaming to have this system back and so
> my time was limited in responding.
It may be better in a situation like this to open a case with Microsoft
support. It's certainly more expensive than a free forum like this one.
Then again, if they can help to reduce downtime, you get the money back
that way. Of course, also the support engineers will ask you questions
about the configuration, error messages etc.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, March 11, 2012
Database restores using Enterprise Manager
Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.
Chris
That the way, restoring the database files one by one.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
database restores - what actually happens?
If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
database restores - what actually happens?
If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
John
Hi
Does this help?
http://msdn.microsoft.com/library/de...ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
John
Hi
Does this help?
http://msdn.microsoft.com/library/de...ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
database restores - what actually happens?
If you have a database with 2 data files and you do a restore will sql serve
r
fill one datafile first before starting on the second, or do both at the sam
e
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/d...br />
49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
r
fill one datafile first before starting on the second, or do both at the sam
e
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/d...br />
49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John
Thursday, March 8, 2012
Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Database restore questions
Hello Gurus,
I wanted to restore a database to multiple data files from backup which has got only few data files. Is it possible do this?
Example : Backup has 4 data files and I need to restore it to 8 data files.
Thanks
Subbu
No. A restore is a 1 for 1 operation. After you are done restoring, you can then add more files/filegroups and move data around if you want to.Database restore question
Hi,
I have a database (or better: used to have) and backup consisting of
- the initial (complete) database
- all log files since then (or so I thought)
After making a data entry error I wrote the log to the backup and
tried a point of time restore.
Unfortunately that failed with the message
"The log in this backup set begins at LSN xxx, which is too late to apply to
the database. An earlier log backup that includes LSN yyy can be restored."
and left the database in an inaccessible state.
I have tried to reproduce the error, my guess is that the recovery model was
set to 'simple'
instead of ' full' at for some time.
Is there anyway I can
- extract data from the log files (however incomplete)?
- get the database back to the point of just after the database error (just
before I tried the restore)?
Thanks for your time!
Andre"Andre" <nonamel@.nospam.org> wrote in message
news:40c0c429$0$33919$e4fe514c@.news.xs4all.nl...
> Hi,
> I have a database (or better: used to have) and backup consisting of
> - the initial (complete) database
> - all log files since then (or so I thought)
> After making a data entry error I wrote the log to the backup and
> tried a point of time restore.
> Unfortunately that failed with the message
> "The log in this backup set begins at LSN xxx, which is too late to apply
to
> the database. An earlier log backup that includes LSN yyy can be
restored."
> and left the database in an inaccessible state.
> I have tried to reproduce the error, my guess is that the recovery model
was
> set to 'simple'
> instead of ' full' at for some time.
> Is there anyway I can
> - extract data from the log files (however incomplete)?
> - get the database back to the point of just after the database error
(just
> before I tried the restore)?
> Thanks for your time!
> Andre
I'm not really sure I follow your description - do you mean that the
sequence of log backups was broken because the database was set to simple
mode, then back to full? So when you restored your logs, only some of them
restored, before giving the error? If so, then you should be able to make it
available again like this:
restore database MyDB with recovery
However you can't roll forward without a full sequence of log backups, so
you won't be able to get back to the point after the error occurred. If the
data is valuable enough, you should probably consider calling Microsoft for
support, but they may not be able to do much either, if you don't have a
valid backup set.
You might be able to recover something from your log backups using a tool
like this one, but if you don't have all the log backups then you won't know
what is missing:
http://www.lumigent.com/products/le_sql/le_sql.htm
If I've misunderstood your situation, or if this isn't helpful, please give
some more detailed information about what happened, what you've tried
(preferably the exact RESTORE commands you used), and the current status of
the database (ie. using DATABASEPROPERTYEX('MyDB', 'Status')).
Simon
Saturday, February 25, 2012
Database recovery with data file only
I had database whose log file and backup files are unavailable due to a media failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message â'Device Activation error. Physical file name â'C:\...\xxx.ldfâ' may be incorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
--
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> >
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > A word of warning regarding the technique proposed by Hari. Forcibly
> > rebuilding the transaction log results in a database with questionable
> > integrity. Data may be physically corrupt or logically inconsistent
> because
> > normal database recovery did not take place.
> >
> > A preferable method is to restore from backup. If the log must be
rebuilt
> > because no backup is available, I suggest data be exported and then
> imported
> > into a clean database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > I had database whose log file and backup files are unavailable due to
a
> > media failure. I had the data file which is up to date.
> > >
> > > Is it possible to recover the database?
> > >
> > > I had tried using attach database utility but failed with an error
> message
> > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > incorrect.
> > >
> > > Please advice me if anything can be done.
> > >
> > > Thanking You
> > >
> >
> >
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> > Hi Dan,
> >
> > I accept what you say regarding data integrity.
> > I suggested /recommended this method only because ken do not have the
> > database Backup as well as
> > no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> > approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that
database
> is
> > fine or not.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > A word of warning regarding the technique proposed by Hari. Forcibly
> > > rebuilding the transaction log results in a database with questionable
> > > integrity. Data may be physically corrupt or logically inconsistent
> > because
> > > normal database recovery did not take place.
> > >
> > > A preferable method is to restore from backup. If the log must be
> rebuilt
> > > because no backup is available, I suggest data be exported and then
> > imported
> > > into a clean database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > > I had database whose log file and backup files are unavailable due
to
> a
> > > media failure. I had the data file which is up to date.
> > > >
> > > > Is it possible to recover the database?
> > > >
> > > > I had tried using attach database utility but failed with an error
> > message
> > > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > > incorrect.
> > > >
> > > > Please advice me if anything can be done.
> > > >
> > > > Thanking You
> > > >
> > >
> > >
> >
> >
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database is
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
> >Hi Dan,
> >
> >I accept what you say regarding data integrity.
> >I suggested /recommended this method only because ken do not have the
> >database Backup as well as
> >no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> >approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that database
is
> >fine or not.
>
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message â'Device Activation error. Physical file name â'C:\...\xxx.ldfâ' may be incorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
--
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> >
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > A word of warning regarding the technique proposed by Hari. Forcibly
> > rebuilding the transaction log results in a database with questionable
> > integrity. Data may be physically corrupt or logically inconsistent
> because
> > normal database recovery did not take place.
> >
> > A preferable method is to restore from backup. If the log must be
rebuilt
> > because no backup is available, I suggest data be exported and then
> imported
> > into a clean database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > I had database whose log file and backup files are unavailable due to
a
> > media failure. I had the data file which is up to date.
> > >
> > > Is it possible to recover the database?
> > >
> > > I had tried using attach database utility but failed with an error
> message
> > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > incorrect.
> > >
> > > Please advice me if anything can be done.
> > >
> > > Thanking You
> > >
> >
> >
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> > Hi Dan,
> >
> > I accept what you say regarding data integrity.
> > I suggested /recommended this method only because ken do not have the
> > database Backup as well as
> > no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> > approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that
database
> is
> > fine or not.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > A word of warning regarding the technique proposed by Hari. Forcibly
> > > rebuilding the transaction log results in a database with questionable
> > > integrity. Data may be physically corrupt or logically inconsistent
> > because
> > > normal database recovery did not take place.
> > >
> > > A preferable method is to restore from backup. If the log must be
> rebuilt
> > > because no backup is available, I suggest data be exported and then
> > imported
> > > into a clean database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > > I had database whose log file and backup files are unavailable due
to
> a
> > > media failure. I had the data file which is up to date.
> > > >
> > > > Is it possible to recover the database?
> > > >
> > > > I had tried using attach database utility but failed with an error
> > message
> > > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > > incorrect.
> > > >
> > > > Please advice me if anything can be done.
> > > >
> > > > Thanking You
> > > >
> > >
> > >
> >
> >
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database is
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
> >Hi Dan,
> >
> >I accept what you say regarding data integrity.
> >I suggested /recommended this method only because ken do not have the
> >database Backup as well as
> >no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> >approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that database
is
> >fine or not.
>
Database recovery with data file only
I had database whose log file and backup files are unavailable due to a media failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message “Device Activation error. Physical file name “C:\...\xxx.ldf” may be incorrect.
Please advice me if anything can be done.
Thanking You
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net... [vbcol=seagreen]
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to[vbcol=seagreen]
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to
> a
>
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message “Device Activation error. Physical file name “C:\...\xxx.ldf” may be incorrect.
Please advice me if anything can be done.
Thanking You
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net... [vbcol=seagreen]
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to[vbcol=seagreen]
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to
> a
>
Subscribe to:
Posts (Atom)