Regarding filegroups

Dear all,
I was just wondering myself how could I do for to obtain to which filegroup
belong a specific table.
Imagine that you have primary, primary_history, secondary_history,
index_history, bla,bla
Any idea or though would be very appreciated.
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)What version of SQL Server? For SQL Server 2000, use the groupid column in s
ysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
> Dear all,
> I was just wondering myself how could I do for to obtain to which filegrou
p
> belong a specific table.
> Imagine that you have primary, primary_history, secondary_history,
> index_history, bla,bla
> Any idea or though would be very appreciated.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||Hi Tibor,
It's ok. That's fine but I'm looking for user tables no indexes.
Where does sql stores that?
I'm seeing sysobjects table with xtype = 'u'
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||The physical aspects if a table is represented in sysindexes. Use WHERE indi
d IN (0,1). See Books
Online, sysindexes for more details about sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9EA44F73-094E-4458-8A0C-FF1BBD34123E@.microsoft.com...
> Hi Tibor,
> It's ok. That's fine but I'm looking for user tables no indexes.
> Where does sql stores that?
> I'm seeing sysobjects table with xtype = 'u'
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>|||I've seen how, using DMO...
Another possibility?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||USE pubs
SELECT sfg.groupname
FROM sysfilegroups AS sfg
INNER JOIN sysindexes AS si
ON si.groupid = sfg.groupid
WHERE si.id = OBJECT_ID('dbo.authors')
AND indid IN(0,1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9A0F784A-A179-4593-9C8A-1226B0941772@.microsoft.com...
> I've seen how, using DMO...
> Another possibility?
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>

Regarding ETL, urgent

Dear all,
I've got two DTS with a lot of transformations and within of them VbScript
snippets and so on. My issue is that both has been modified incorrectly but
I
haven't idea where and how. So this way I was wondering if exists any
third-tool on the market (I'm not thinking in the own Sql Server) which be
able to compare automatically two dts from the most low level. Otherwise I'l
l
be to compare section by section using WinMerge or something like that.
Any input or comment would be very welcomed.You have a few options :-
DTS keeps a version history that will enable you to rollback to previous
package (Simply right click the package and choose version)
Red Gate do a package called "DTS Compare"
Restore msdb from backups to another server and extract the package you
require.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
> Dear all,
> I've got two DTS with a lot of transformations and within of them VbScript
> snippets and so on. My issue is that both has been modified incorrectly
> but I
> haven't idea where and how. So this way I was wondering if exists any
> third-tool on the market (I'm not thinking in the own Sql Server) which be
> able to compare automatically two dts from the most low level. Otherwise
> I'll
> be to compare section by section using WinMerge or something like that.
> Any input or comment would be very welcomed.
>|||Thanks in advance Ryan,
"Ryan" wrote:

> You have a few options :-
> DTS keeps a version history that will enable you to rollback to previo
us
> package (Simply right click the package and choose version)
> Red Gate do a package called "DTS Compare"
> Restore msdb from backups to another server and extract the package yo
u
> require.
> --
> HTH. Ryan
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
>
>|||Hi Enric
as a built-in alternative - not as slick I'm sure as a dedicated tool
such as DTS Compare - you can save any DTS package as a VB module. So
assuming you're comfortable with VB, you should be able to compare the
two packages by extracting them both to VB modules and comparing the
two files. Even if you don't have Visual Studio, a basic file-compare
utility should be able to show you the differences.
cheers
Seb

Refresh problem in Crystal Viewer control

Dear Guru's
I am using Crystal viewer control in my VC++ application project. Here i am facing report refresh problem. If i call viewer control Refresh method directly the application is crashing. I am calling that inside OnPaint( ) .

The problem is the report data is not displaying first time the user has to click refresh button.

Please help me how to use "Refresh " method.

Thanks
arunNot sure if this will help, but make sure 'Save Data with Report' is NOT checked. Otherwise, have you tried calling Refresh as soon as the report is displayed (instead of waiting for the user to do it)?|||I am trying to refresh the report through code only using Viewer control 'Refresh' method.
If report displayed with blank then how user came to know that he has to refresh the report by clicking refresh button.

Referential Integrity constraints

Dear Sir,
Yesterday during an Interview...i have faced some problem. I would like to
clearify that NN (NOT NULL), ND (NOT DUPLICATE) AND NC(NOT CHANGE)
constraints work ...when we define a Primary Key on any Table column. If,
Yes( as i hope) then how and what's the meaning of this NC. Can You Plz. giv
e
me briefly explain and can we use this in ORACLE .> ...me briefly explain and can we use this in ORACLE .
This is a SQL Server forum. You probably get (better) help in an Oracle foru
m.
In SQL Server, defining a PK doesn't prohibit you to change the value of the
PK column for one or
several rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjeev Kumar Sinha" <Sanjeev Kumar Sinha@.discussions.microsoft.com> wrote
in message
news:E7240299-73E3-4C56-891D-66F4CB533174@.microsoft.com...
> Dear Sir,
> Yesterday during an Interview...i have faced some problem. I would like to
> clearify that NN (NOT NULL), ND (NOT DUPLICATE) AND NC(NOT CHANGE)
> constraints work ...when we define a Primary Key on any Table column. If,
> Yes( as i hope) then how and what's the meaning of this NC. Can You Plz. g
ive
> me briefly explain and can we use this in ORACLE .
>
>|||Yeah, well now I would like it to :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHOkklxHFHA.156@.TK2MSFTNGP10.phx.gbl...
> This is a SQL Server forum. You probably get (better) help in an Oracle
> forum.
> In SQL Server, defining a PK doesn't prohibit you to change the value of
> the PK column for one or several rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sanjeev Kumar Sinha" <Sanjeev Kumar Sinha@.discussions.microsoft.com>
> wrote in message
> news:E7240299-73E3-4C56-891D-66F4CB533174@.microsoft.com...
>|||You have to talk to the ANSI SQL committee about that, Louis. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OCdomJ1HFHA.3076@.tk2msftngp13.phx.gbl...
> Yeah, well now I would like it to :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisp...r />
l?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in con
sulting services. All
> other replies may be ignored :)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHOkklxHFHA.156@.TK2MSFTNGP10.phx.gbl...

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
Neil
Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>
|||Have a look at
http://www.microsoft.com/sql/techinf...ailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techin...vailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHave you looked at Replication yet ?
With this you can automatically copy information for one
database to another automatically.
Look up Snapshot and Transactional Replication.
J
>--Original Message--
>Dear All
>We have a server running Windows 2000 Server with SQL
2000. This is our web
>server, and what I would like to do is implement some
form of redundancy
>server within this environment. Ideally, what I would
like to achieve is a
>scenario where is if there is a failure on one of the
servers another one
>would start up ad take on the exact same role with the
exact same access and
>sql database and applications.
>Does anyone have any idea how this would be achieved, and
point in the right
>direction.
>Thanks
>Neil
>
>.
>|||Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techinfo/administration/2000/availability.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
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.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
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.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
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.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
--
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> > Dear all,
> >
> > I want to reduce the size of log file, what should I do ?
> > Thanks.
> >
> >
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Reduce Log File Size

Dear Guys
How to Reduce the log file size in SQL2000. I have used DBCC
Shrinkfile(db_log,filesize)
but it doesn't work. The Log file size still the same
And my HD Size will be full. Have any suggestion.
thanks
Have a look at these:
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=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"cf_jian" <fongjian@.elanvital.com.tw> wrote in message
news:eBiGQK2KEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Dear Guys
> How to Reduce the log file size in SQL2000. I have used DBCC
> Shrinkfile(db_log,filesize)
> but it doesn't work. The Log file size still the same
> And my HD Size will be full. Have any suggestion.
> thanks
>
|||There could be different reasons for that behavior. However, I would
suggest the following:
"The DBCC SHRINKFILE and DBCC SHRINKDATABASE commands will not move data
and reduce the file size if the EstimatedPages value equals the MinimumSize
value that is reported when the command completes"
For this, have a look at the following KB Article:
http://support.microsoft.com/default...&Product=sql2k
Also, do you get some kind of error message? Try looking into the error
logs and see if that helps you to point to something.
Also, for general help on this command, please review:
http://support.microsoft.com/default...&Product=sql2k
Hope that it helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.