Question regarding sp_detach

Ran this command - EXEC sp_detach_db 'testlog', 'true'
Deleted the .ldf file associated with the database and
then ran this:
EXEC sp_attach_single_file_db @.dbname = 'testlog',
@.physname = 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_data.mdf'
Then I got this error message
"Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\testlog_log.ldf' may be incorrect.
New log file 'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\testlog_log.LDF' was created."
Is this an expected error message?
Thanks Mike Allen
Novice DBAMike,
I think the message is more information, letting you know that SQL Server
created a log file for you. Note, however, that BOL states that a new log
file will/can be created only if you actually detached the database first
and only of you have one and only one log file and only one database file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Allen" <mikea@.atg.wa.gov> wrote in message
news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
> Ran this command - EXEC sp_detach_db 'testlog', 'true'
> Deleted the .ldf file associated with the database and
> then ran this:
> EXEC sp_attach_single_file_db @.dbname = 'testlog',
> @.physname = 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_data.mdf'
> Then I got this error message
> "Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
> New log file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testlog_log.LDF' was created."
> Is this an expected error message?
> Thanks Mike Allen
> Novice DBA
>|||Thanks
>--Original Message--
>Mike,
>I think the message is more information, letting you know
that SQL Server
>created a log file for you. Note, however, that BOL
states that a new log
>file will/can be created only if you actually detached
the database first
>and only of you have one and only one log file and only
one database file.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mike Allen" <mikea@.atg.wa.gov> wrote in message
>news:1157001c3bf6c$a15d8620$a601280a@.phx.gbl...
>> Ran this command - EXEC sp_detach_db 'testlog', 'true'
>> Deleted the .ldf file associated with the database and
>> then ran this:
>> EXEC sp_attach_single_file_db @.dbname = 'testlog',
>> @.physname = 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_data.mdf'
>> Then I got this error message
>> "Device activation error. The physical file
>> name 'D:\Program Files\Microsoft SQL
>> Server\MSSQL\data\testlog_log.ldf' may be incorrect.
>> New log file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\testlog_log.LDF' was created."
>> Is this an expected error message?
>> Thanks Mike Allen
>> Novice DBA
>
>.
>sql

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.
|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.
|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
Thanks,
John
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon
|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server, a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb further
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.
|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas

"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon
sql

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
--
Thanks,
Johnhard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/defaul...kb;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server,
a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb furth
er
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/defaul...kb;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
--
Thanks,
Johnhard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server, a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb further
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon

Question on a large .LDF file

Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!See: http://dbforums.com/showthread.php?threadid=561221|||DBA's solution is a good one-time fix and one that I have used before. For an ongoing solution, you will need to check a few things:

1. What's your business requirement? Up to the point of failure recovery, or something less than that?

2. What backup media options do you have available? Tape? Disk?

Depending on your answers to 1 and 2, you will need to create a backup strategy for the database. I suspect from your initial problem statement that you do not have database backups (aka dumps) enabled. I further suspect that the database is set for full transaction logging. See note below

See the SQL books on line, or just try walking through the Database Maintenance wizard.

To give you an idea, we have three strategies in our organization:

1. Transaction Logging. Where we backup the database and ship the log files over to another server for a "warm spare" capability. This is expensive and requires a fair knowledge of backups and SQL Administration.

2. Full and Transaction Log Backup to Disk. We have sufficient disk space on a separate partition, we do full backups daily and transaction log backups every hour. The backups are all stored to disk for 24 hours. In that time, they are copied to tape for archive retrieval. A variant is to back everything up directly to tape, but that is slow and may impact SQL performance.

3. Simple Recovery. A few of our databases (mostly development databases) do not need point-in-time recovery capability. These are set up to use Simple logging. This keeps the transaction log (.LDF file) nice and small and simplifies the backup plan (we back the full database once daily). But you can only restore the database to the last time it was fully backed up (meaning wwe could potentially lose 24 hours' worth of work).

Note: If I am wrong about the database backups not being enabled, then I humbly apologize. It's just that that's how I learned my lesson (the hard way). If you do have backup plans enabled, you might investigate shrinking the amount of time between transaction log backups.

HTH,

Hugh Scott

Originally posted by bprell
Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!