Is it possible to relocate the data and/or the log files after they have bee
n
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have b
een
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Friday, March 30, 2012
MDF and LDF file locations
Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.
|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
assigned a location? Thanks everyone for your help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.
|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
MDF and LDF file locations
Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.sql
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.sql
mdf and ldf file extensions
Hello All,
One of my customers created a database in SQL 2K SP3a. He gave the data
file LDF extension and log file MDF extension.
When I do sp_helpdb 'dbname', the extensions are pointing to the correct
files, meaning LDF is pointing to the data file and MDF is pointing to the
log file just as he designed.
My question is: is it just a standard suggested by Microsoft to name the
data file with MDF extension and log file with LDF and NDF extensions? Is
there going to be any repercussions later on if we name the files with wrong
extensions.
I though it was odd that SQL Server let the files be named with wrong
extensions in the first place.
Any ideas?
Thanks,
BivaThis will not confuse SQL Server. SQL Server doesn't rely
on the file name extension to determine the type of the
file or how it deals with the file. But it may be
confusing to a DBA, and wouldn't be considered best
practice from a naming convention standpoint.
If you don't like the current naming, you can detach the
database, rename the files to the conventional extensions,
and then attach the database using the renamed files.
Linchi
>--Original Message--
>Hello All,
>One of my customers created a database in SQL 2K SP3a.
He gave the data
>file LDF extension and log file MDF extension.
>When I do sp_helpdb 'dbname', the extensions are pointing
to the correct
>files, meaning LDF is pointing to the data file and MDF
is pointing to the
>log file just as he designed.
>My question is: is it just a standard suggested by
Microsoft to name the
>data file with MDF extension and log file with LDF and
NDF extensions? Is
>there going to be any repercussions later on if we name
the files with wrong
>extensions.
>I though it was odd that SQL Server let the files be
named with wrong
>extensions in the first place.
>Any ideas?
>Thanks,
>Biva
>
>.
>|||> My question is: is it just a standard suggested by Microsoft to name the
> data file with MDF extension and log file with LDF and NDF extensions?
The pedantic part of me is tugging to correct this. By default, at least.
Data files have MDF (and NDF extensions, in the case of multiple files).
Log files have LDF extensions (whether singular or multiple).
You can certainly override this behavior. I'm not certain that there are
any advantages of doing so, and I can certainly think of some drawbacks.
But there it is.
--
Aaron Bertrand
SQL Server MVP|||Hi Biva,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You concern is if the MDF, NDF, LDF is just a suggested standard by
Microsoft and if there problem when change the extension to these files,
right?
When create a database, you should set a filename of the data, log, etc.
These fine name and extension should be specified (they are 'os_file_name'
when you refer it to 'create database' in BOL, SQL Server Books Online).
Although 'os_file_name' can be any valid operating system file name, the
name more clearly reflects the purpose of the file if you use the following
recommended extensions.
You can create a database like this:
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdfx',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldfx',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
As we know, the system table 'master' will control the user databases and
operation of SQL Server as a whole by keeping track of all information of
them. When you use:
Use master
Go
Select * from sysdatabases
You will get the fileinformation of sales with the extension you defined,
that is
'c:\program files\microsoft sql server\mssql\data\saledat.mdfx'
and you can create table or execute other operations on it.
But if you revise the extension of the file and thenuse the database again,
the SQL Server will notify you that the file is not exist although there
may be someway de recover. It is strongly suggested you to keep the
suggested file extension by Microsoft.
Hope this answered your questions. If you still have more concern about it,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
One of my customers created a database in SQL 2K SP3a. He gave the data
file LDF extension and log file MDF extension.
When I do sp_helpdb 'dbname', the extensions are pointing to the correct
files, meaning LDF is pointing to the data file and MDF is pointing to the
log file just as he designed.
My question is: is it just a standard suggested by Microsoft to name the
data file with MDF extension and log file with LDF and NDF extensions? Is
there going to be any repercussions later on if we name the files with wrong
extensions.
I though it was odd that SQL Server let the files be named with wrong
extensions in the first place.
Any ideas?
Thanks,
BivaThis will not confuse SQL Server. SQL Server doesn't rely
on the file name extension to determine the type of the
file or how it deals with the file. But it may be
confusing to a DBA, and wouldn't be considered best
practice from a naming convention standpoint.
If you don't like the current naming, you can detach the
database, rename the files to the conventional extensions,
and then attach the database using the renamed files.
Linchi
>--Original Message--
>Hello All,
>One of my customers created a database in SQL 2K SP3a.
He gave the data
>file LDF extension and log file MDF extension.
>When I do sp_helpdb 'dbname', the extensions are pointing
to the correct
>files, meaning LDF is pointing to the data file and MDF
is pointing to the
>log file just as he designed.
>My question is: is it just a standard suggested by
Microsoft to name the
>data file with MDF extension and log file with LDF and
NDF extensions? Is
>there going to be any repercussions later on if we name
the files with wrong
>extensions.
>I though it was odd that SQL Server let the files be
named with wrong
>extensions in the first place.
>Any ideas?
>Thanks,
>Biva
>
>.
>|||> My question is: is it just a standard suggested by Microsoft to name the
> data file with MDF extension and log file with LDF and NDF extensions?
The pedantic part of me is tugging to correct this. By default, at least.
Data files have MDF (and NDF extensions, in the case of multiple files).
Log files have LDF extensions (whether singular or multiple).
You can certainly override this behavior. I'm not certain that there are
any advantages of doing so, and I can certainly think of some drawbacks.
But there it is.
--
Aaron Bertrand
SQL Server MVP|||Hi Biva,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You concern is if the MDF, NDF, LDF is just a suggested standard by
Microsoft and if there problem when change the extension to these files,
right?
When create a database, you should set a filename of the data, log, etc.
These fine name and extension should be specified (they are 'os_file_name'
when you refer it to 'create database' in BOL, SQL Server Books Online).
Although 'os_file_name' can be any valid operating system file name, the
name more clearly reflects the purpose of the file if you use the following
recommended extensions.
You can create a database like this:
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdfx',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldfx',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
As we know, the system table 'master' will control the user databases and
operation of SQL Server as a whole by keeping track of all information of
them. When you use:
Use master
Go
Select * from sysdatabases
You will get the fileinformation of sales with the extension you defined,
that is
'c:\program files\microsoft sql server\mssql\data\saledat.mdfx'
and you can create table or execute other operations on it.
But if you revise the extension of the file and thenuse the database again,
the SQL Server will notify you that the file is not exist although there
may be someway de recover. It is strongly suggested you to keep the
suggested file extension by Microsoft.
Hope this answered your questions. If you still have more concern about it,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Wednesday, March 21, 2012
MaxSize on Transaction_Log
Hello,
If I set a MaxSize on my transaction log, what happens when that size is
reached? Will it automatically overwrite old data, or will it be an error?
Our transaction log has suddenly become real huge. I have read a lot in this
list about this, but I haven't found anything about what happens with the
MaxSize enabled.
Regards,
/Mikael SWhat recovery model do you have the database set to..
--
HTH
Ryan Waight, MCDBA, MCSE
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> Hello,
> If I set a MaxSize on my transaction log, what happens when that size is
> reached? Will it automatically overwrite old data, or will it be an error?
> Our transaction log has suddenly become real huge. I have read a lot in
this
> list about this, but I haven't found anything about what happens with the
> MaxSize enabled.
> Regards,
> /Mikael S
>|||I have Recovery Model: FULL
The Transaction Log is set to Automatically Growth
Auto Shrink is not set
I use SQL2000 on a SRV2000.
Thank you for your reply,
/Mikael S
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> What recovery model do you have the database set to..
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > If I set a MaxSize on my transaction log, what happens when that size is
> > reached? Will it automatically overwrite old data, or will it be an
error?
> > Our transaction log has suddenly become real huge. I have read a lot in
> this
> > list about this, but I haven't found anything about what happens with
the
> > MaxSize enabled.
> >
> > Regards,
> >
> > /Mikael S
> >
> >
>|||If you reach maxsize, then the connections that executes commands which need to write to the log
(all modification commands, like INSERT, UPDATE, DELETE etc) will receive error number 9002.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> I have Recovery Model: FULL
> The Transaction Log is set to Automatically Growth
> Auto Shrink is not set
> I use SQL2000 on a SRV2000.
> Thank you for your reply,
> /Mikael S
>
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > What recovery model do you have the database set to..
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > If I set a MaxSize on my transaction log, what happens when that size is
> > > reached? Will it automatically overwrite old data, or will it be an
> error?
> > > Our transaction log has suddenly become real huge. I have read a lot in
> > this
> > > list about this, but I haven't found anything about what happens with
> the
> > > MaxSize enabled.
> > >
> > > Regards,
> > >
> > > /Mikael S
> > >
> > >
> >
> >
>|||OK I see.
Is there a way to analyze the Transaction Log to see what caused the file to
rapidly grow the last couple of days? It is now up to 20GB, the database is
400MB. This has worked fine for a year, but the latest days (perhaphs some
weeks) the log has "gone mad".
We use some Stored Procedures and connections, but not very big
transactions. I have read the KBs earlier posted here, and I will backup and
shrink the database (log) this evening. It would be interesting to see what
caused the log to be so big suddenly.
Regards,
/Mikael S
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
skrev i meddelandet news:OPWXmHOtDHA.2340@.TK2MSFTNGP12.phx.gbl...
> If you reach maxsize, then the connections that executes commands which
need to write to the log
> (all modification commands, like INSERT, UPDATE, DELETE etc) will receive
error number 9002.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> > I have Recovery Model: FULL
> > The Transaction Log is set to Automatically Growth
> > Auto Shrink is not set
> >
> > I use SQL2000 on a SRV2000.
> >
> > Thank you for your reply,
> >
> > /Mikael S
> >
> >
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> > news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> >
> > > What recovery model do you have the database set to..
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > > Hello,
> > > >
> > > > If I set a MaxSize on my transaction log, what happens when that
size is
> > > > reached? Will it automatically overwrite old data, or will it be an
> > error?
> > > > Our transaction log has suddenly become real huge. I have read a lot
in
> > > this
> > > > list about this, but I haven't found anything about what happens
with
> > the
> > > > MaxSize enabled.
> > > >
> > > > Regards,
> > > >
> > > > /Mikael S
> > > >
> > > >
> > >
> > >
> >
> >
>|||It is possible to view the Tran log with a 3rd party tool lumigent log
explorer. In the meantime have a read through the following articles on Tran
Log :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:ePlNYYOtDHA.700@.TK2MSFTNGP11.phx.gbl...
> OK I see.
> Is there a way to analyze the Transaction Log to see what caused the file
to
> rapidly grow the last couple of days? It is now up to 20GB, the database
is
> 400MB. This has worked fine for a year, but the latest days (perhaphs some
> weeks) the log has "gone mad".
> We use some Stored Procedures and connections, but not very big
> transactions. I have read the KBs earlier posted here, and I will backup
and
> shrink the database (log) this evening. It would be interesting to see
what
> caused the log to be so big suddenly.
> Regards,
> /Mikael S
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> skrev i meddelandet news:OPWXmHOtDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > If you reach maxsize, then the connections that executes commands which
> need to write to the log
> > (all modification commands, like INSERT, UPDATE, DELETE etc) will
receive
> error number 9002.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> > > I have Recovery Model: FULL
> > > The Transaction Log is set to Automatically Growth
> > > Auto Shrink is not set
> > >
> > > I use SQL2000 on a SRV2000.
> > >
> > > Thank you for your reply,
> > >
> > > /Mikael S
> > >
> > >
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> > > news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > >
> > > > What recovery model do you have the database set to..
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > > > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > > > Hello,
> > > > >
> > > > > If I set a MaxSize on my transaction log, what happens when that
> size is
> > > > > reached? Will it automatically overwrite old data, or will it be
an
> > > error?
> > > > > Our transaction log has suddenly become real huge. I have read a
lot
> in
> > > > this
> > > > > list about this, but I haven't found anything about what happens
> with
> > > the
> > > > > MaxSize enabled.
> > > > >
> > > > > Regards,
> > > > >
> > > > > /Mikael S
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
If I set a MaxSize on my transaction log, what happens when that size is
reached? Will it automatically overwrite old data, or will it be an error?
Our transaction log has suddenly become real huge. I have read a lot in this
list about this, but I haven't found anything about what happens with the
MaxSize enabled.
Regards,
/Mikael SWhat recovery model do you have the database set to..
--
HTH
Ryan Waight, MCDBA, MCSE
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> Hello,
> If I set a MaxSize on my transaction log, what happens when that size is
> reached? Will it automatically overwrite old data, or will it be an error?
> Our transaction log has suddenly become real huge. I have read a lot in
this
> list about this, but I haven't found anything about what happens with the
> MaxSize enabled.
> Regards,
> /Mikael S
>|||I have Recovery Model: FULL
The Transaction Log is set to Automatically Growth
Auto Shrink is not set
I use SQL2000 on a SRV2000.
Thank you for your reply,
/Mikael S
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> What recovery model do you have the database set to..
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > If I set a MaxSize on my transaction log, what happens when that size is
> > reached? Will it automatically overwrite old data, or will it be an
error?
> > Our transaction log has suddenly become real huge. I have read a lot in
> this
> > list about this, but I haven't found anything about what happens with
the
> > MaxSize enabled.
> >
> > Regards,
> >
> > /Mikael S
> >
> >
>|||If you reach maxsize, then the connections that executes commands which need to write to the log
(all modification commands, like INSERT, UPDATE, DELETE etc) will receive error number 9002.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> I have Recovery Model: FULL
> The Transaction Log is set to Automatically Growth
> Auto Shrink is not set
> I use SQL2000 on a SRV2000.
> Thank you for your reply,
> /Mikael S
>
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > What recovery model do you have the database set to..
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > If I set a MaxSize on my transaction log, what happens when that size is
> > > reached? Will it automatically overwrite old data, or will it be an
> error?
> > > Our transaction log has suddenly become real huge. I have read a lot in
> > this
> > > list about this, but I haven't found anything about what happens with
> the
> > > MaxSize enabled.
> > >
> > > Regards,
> > >
> > > /Mikael S
> > >
> > >
> >
> >
>|||OK I see.
Is there a way to analyze the Transaction Log to see what caused the file to
rapidly grow the last couple of days? It is now up to 20GB, the database is
400MB. This has worked fine for a year, but the latest days (perhaphs some
weeks) the log has "gone mad".
We use some Stored Procedures and connections, but not very big
transactions. I have read the KBs earlier posted here, and I will backup and
shrink the database (log) this evening. It would be interesting to see what
caused the log to be so big suddenly.
Regards,
/Mikael S
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
skrev i meddelandet news:OPWXmHOtDHA.2340@.TK2MSFTNGP12.phx.gbl...
> If you reach maxsize, then the connections that executes commands which
need to write to the log
> (all modification commands, like INSERT, UPDATE, DELETE etc) will receive
error number 9002.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> > I have Recovery Model: FULL
> > The Transaction Log is set to Automatically Growth
> > Auto Shrink is not set
> >
> > I use SQL2000 on a SRV2000.
> >
> > Thank you for your reply,
> >
> > /Mikael S
> >
> >
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> > news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> >
> > > What recovery model do you have the database set to..
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > > Hello,
> > > >
> > > > If I set a MaxSize on my transaction log, what happens when that
size is
> > > > reached? Will it automatically overwrite old data, or will it be an
> > error?
> > > > Our transaction log has suddenly become real huge. I have read a lot
in
> > > this
> > > > list about this, but I haven't found anything about what happens
with
> > the
> > > > MaxSize enabled.
> > > >
> > > > Regards,
> > > >
> > > > /Mikael S
> > > >
> > > >
> > >
> > >
> >
> >
>|||It is possible to view the Tran log with a 3rd party tool lumigent log
explorer. In the meantime have a read through the following articles on Tran
Log :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:ePlNYYOtDHA.700@.TK2MSFTNGP11.phx.gbl...
> OK I see.
> Is there a way to analyze the Transaction Log to see what caused the file
to
> rapidly grow the last couple of days? It is now up to 20GB, the database
is
> 400MB. This has worked fine for a year, but the latest days (perhaphs some
> weeks) the log has "gone mad".
> We use some Stored Procedures and connections, but not very big
> transactions. I have read the KBs earlier posted here, and I will backup
and
> shrink the database (log) this evening. It would be interesting to see
what
> caused the log to be so big suddenly.
> Regards,
> /Mikael S
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> skrev i meddelandet news:OPWXmHOtDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > If you reach maxsize, then the connections that executes commands which
> need to write to the log
> > (all modification commands, like INSERT, UPDATE, DELETE etc) will
receive
> error number 9002.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> news:O9JIaBOtDHA.1088@.tk2msftngp13.phx.gbl...
> > > I have Recovery Model: FULL
> > > The Transaction Log is set to Automatically Growth
> > > Auto Shrink is not set
> > >
> > > I use SQL2000 on a SRV2000.
> > >
> > > Thank you for your reply,
> > >
> > > /Mikael S
> > >
> > >
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> skrev i meddelandet
> > > news:uvN4pMNtDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > >
> > > > What recovery model do you have the database set to..
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Mikael" <mikael@.DELETETHISslk.nu> wrote in message
> > > > news:%23gxY%23QMtDHA.1788@.tk2msftngp13.phx.gbl...
> > > > > Hello,
> > > > >
> > > > > If I set a MaxSize on my transaction log, what happens when that
> size is
> > > > > reached? Will it automatically overwrite old data, or will it be
an
> > > error?
> > > > > Our transaction log has suddenly become real huge. I have read a
lot
> in
> > > > this
> > > > > list about this, but I haven't found anything about what happens
> with
> > > the
> > > > > MaxSize enabled.
> > > > >
> > > > > Regards,
> > > > >
> > > > > /Mikael S
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Maxing out log files when using SIMPLE
We have our databases set to SIMPLE model, but we continue to blow up during DataWarehouse loads because the log file fills. Our understanding is that SIMPLE should have no logging yet it appers to. I have 2 requests.
1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?
2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?
Thanks,
Todd1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.
2) Probably. The culprit is ikely to be your code. I would guess there is either:
a) A begin transaction statement somewhere way at the top of the process
or
b) the BCP/DTS load command is not batching the rows it is inserting.|||I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.
Thanks|||If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.|||With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.
A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.|||Thanks for all the input
1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?
2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?
Thanks,
Todd1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.
2) Probably. The culprit is ikely to be your code. I would guess there is either:
a) A begin transaction statement somewhere way at the top of the process
or
b) the BCP/DTS load command is not batching the rows it is inserting.|||I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.
Thanks|||If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.|||With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.
A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.|||Thanks for all the input
Maximum Worker Threads
SQL Server had reached it's maximum (255) worker threads during the
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
Monday, March 19, 2012
Maximum Worker Threads
SQL Server had reached it's maximum (255) worker threads during the
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,
What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,
What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
Wednesday, March 7, 2012
maximum datafile/log file size
Is there any limit to the maximum size of a datafile or transaction log you can have with SQL Server 2000 on Windows 2000. Also is there a maximum size that should be adhered to for performance and admin reasons ?.What number do you have in mind? The limitation is implemented through a Growth Limit setting if AutoGrowth is enabled. If Growth Limit is not set then it will be limited by the amount of free space on the disk.|||Books Online {Maximum Capacity Specifications}
Database size 1,048,516 TB ***
Databases per instance of SQL Server32,767
Filegroups per database 256
Files per database 32,767
File size (data) 32 TB
File size (log) 32 TB
footnote ***
The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower.
in addition, Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.|||The database I have in mind is going to grow to be order of Terabytes in size, my question about size limits has been answered but can someone put this into the context of performance and admin implications.|||there is no one fix for this issue.
querying large sets of data has many obstacles to overcome.
index creation:
clustered, non-clustered, composite, indexed views, computed columns
query creation:
Probably the most misunderstood part of the dba's job.
what are your indexes?
what are yhour search arguments?
are you computing columns in the queries?
join strategies
and too many others
keep your result sets as small as you can and use effective search arguments.
use stored procedures views create statistics on commonly searched columns that will not be indexed.
Dont forget that transactions and queries are natural enemies and there is a great benefit to creating an OLAP solution for decision support services.
Microsoft SQL Server 2000 Performance Tuning Technical Reference (http://www.microsoft.com/MSPress/books/4944.asp)
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx)
Database size 1,048,516 TB ***
Databases per instance of SQL Server32,767
Filegroups per database 256
Files per database 32,767
File size (data) 32 TB
File size (log) 32 TB
footnote ***
The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower.
in addition, Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.|||The database I have in mind is going to grow to be order of Terabytes in size, my question about size limits has been answered but can someone put this into the context of performance and admin implications.|||there is no one fix for this issue.
querying large sets of data has many obstacles to overcome.
index creation:
clustered, non-clustered, composite, indexed views, computed columns
query creation:
Probably the most misunderstood part of the dba's job.
what are your indexes?
what are yhour search arguments?
are you computing columns in the queries?
join strategies
and too many others
keep your result sets as small as you can and use effective search arguments.
use stored procedures views create statistics on commonly searched columns that will not be indexed.
Dont forget that transactions and queries are natural enemies and there is a great benefit to creating an OLAP solution for decision support services.
Microsoft SQL Server 2000 Performance Tuning Technical Reference (http://www.microsoft.com/MSPress/books/4944.asp)
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx)
Subscribe to:
Posts (Atom)