Hi all,
How to reduce the physical file size of Transaction log in
SQL 7.0?>--Original Message--
>Hi all,
>How to reduce the physical file size of Transaction log
in
>SQL 7.0?
>.
>Ytan, this has happened to me. The hard drive on the SQL
box was full.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ytan" <y24042003@.yahoo.com> wrote in message news:0fea01c38887$5bde8ca0$a401280a@.phx.gbl...
> Hi all,
> How to reduce the physical file size of Transaction log in
> SQL 7.0?sql
Showing posts with label reduce. Show all posts
Showing posts with label reduce. Show all posts
Friday, March 23, 2012
How to reduce the log file ?
Hi,
I found that there is a setting to set the maximum log file, however,
I haven't enable it. Therefore, current log file is 1G. How can I
reduce it to 500MB so that I can set the max. log file to 500MB.
Happy new year to all
Ivan
GinolaIn the future you might post something like this to the
public.SQLserver.server newsgroups. To reduce the log you have two options,
(1) if you do not require point in time recovery you can issue the command
"backup log dbname with truncate_only" Then issue a dbcc shrinkfile command
to shrink the log (see BOL for full syntax). Also, if you do not require
point in time recovery you might set your recovery mode to simple to avoid
the log from growing.
If you do require point in time recovery, setup regular log backups and
don't shrink your logfile unless you have to for space reasons. Having empty
space in the log file is better than having SQL Server reallocate more disk
space to the log during the production day.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...
dbcc shrinkfile.
If you don't need the backup, do
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink as far as possible, set target_size to 0.
For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
(<database_name> )
Whatever you do, don't delete the transaction log. You were lucky that
there were no transactions running or recovering at the time, otherwise, you
would have wound up with a suspect database.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:
Ginola|||I am a beginner in SQL server and I am using SQL server 2000
where should I type
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink the transaction log.
I know how to shrink the database ( because it has the option to
option when right click, but not the log file )
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:
Ginola|||Go to start/programs - microsoft SQL Server - Query Analyzer. You will
need to make a connection to the SQL Server you need to issue the
commands on.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||thanks, got it
On Sun, 04 Jan 2004 18:10:24 -0800, Ray Higdon
<rayhigdon@.higdonconsulting.com> wrote:
Ginola|||Hi,
I have tried
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
but the error message display
"Could not locate file 'sqlBackup' in sysfiles.
DBCC execution completeted. If DBCC printed error message, contact
your system administrator"
sqlBackup is the database name,
When I delete the line "dbcc shrinkfile ... " it works fine. However,
when I apply the third line, the error message display.
Any clue how to solve the problem.
thanks
Ginola|||You need to specify the actual name of the log file for that operation
once you are in the database, try this:
backup log sqlbackup with no_log
go
use sqlbackup
go
dbcc shrinkfile (logical name of your log file,0)
You can find the logical name by right-clicking your database in EM and
clicking the log tab and using the logical name (not the physical MDF
name)
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
I found that there is a setting to set the maximum log file, however,
I haven't enable it. Therefore, current log file is 1G. How can I
reduce it to 500MB so that I can set the max. log file to 500MB.
Happy new year to all
Ivan
GinolaIn the future you might post something like this to the
public.SQLserver.server newsgroups. To reduce the log you have two options,
(1) if you do not require point in time recovery you can issue the command
"backup log dbname with truncate_only" Then issue a dbcc shrinkfile command
to shrink the log (see BOL for full syntax). Also, if you do not require
point in time recovery you might set your recovery mode to simple to avoid
the log from growing.
If you do require point in time recovery, setup regular log backups and
don't shrink your logfile unless you have to for space reasons. Having empty
space in the log file is better than having SQL Server reallocate more disk
space to the log during the production day.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...
quote:|||To shrink the transaction log, you merely need to back it up and execute
> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola
dbcc shrinkfile.
If you don't need the backup, do
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink as far as possible, set target_size to 0.
For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
(<database_name> )
Whatever you do, don't delete the transaction log. You were lucky that
there were no transactions running or recovering at the time, otherwise, you
would have wound up with a suspect database.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...
quote:|||thanks for replying
> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:
quote:
>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.
Ginola|||I am a beginner in SQL server and I am using SQL server 2000
where should I type
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink the transaction log.
I know how to shrink the database ( because it has the option to
option when right click, but not the log file )
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:
quote:
>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.
Ginola|||Go to start/programs - microsoft SQL Server - Query Analyzer. You will
need to make a connection to the SQL Server you need to issue the
commands on.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||thanks, got it
On Sun, 04 Jan 2004 18:10:24 -0800, Ray Higdon
<rayhigdon@.higdonconsulting.com> wrote:
quote:
>Go to start/programs - microsoft SQL Server - Query Analyzer. You will
>need to make a connection to the SQL Server you need to issue the
>commands on.
>HTH
>Ray Higdon MCSE, MCDBA, CCNA
>*** Sent via Developersdex http://www.examnotes.net ***
>Don't just participate in USENET...get rewarded for it!
Ginola|||Hi,
I have tried
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
but the error message display
"Could not locate file 'sqlBackup' in sysfiles.
DBCC execution completeted. If DBCC printed error message, contact
your system administrator"
sqlBackup is the database name,
When I delete the line "dbcc shrinkfile ... " it works fine. However,
when I apply the third line, the error message display.
Any clue how to solve the problem.
thanks
quote:
>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.
Ginola|||You need to specify the actual name of the log file for that operation
once you are in the database, try this:
backup log sqlbackup with no_log
go
use sqlbackup
go
dbcc shrinkfile (logical name of your log file,0)
You can find the logical name by right-clicking your database in EM and
clicking the log tab and using the logical name (not the physical MDF
name)
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
How to reduce size of sql.log file
Hi,
I am running database on SQL2000 server and sql.log file
that is associated with the database keeps on growing. I
would like to reduce its size but don't know how. I would
appreciate if someone could tell me what is the way to
reduce the sql.log file size.
Thanks in advance.
I did try to backup database, backup log file and shrink
both database and log file, but these actions did not
reduce size of sql.log file.
My database file is:
D:\pgm_db\OrdersManager_Data.MDF
My database log file is:
C:\Program Files\Microsoft SQL
Server\MSSQL\data\OrdersManager_Log.LDF
sql.log file is ascii file in the same directory as
database file. The contents of the file looks like this:
with_subscribe_ 618-c6c ENTER SQLAllocEnv
HENV * 010FF7B0
with_subscribe_ 618-c6c EXIT SQLAllocEnv with return
code 0 (SQL_SUCCESS)
HENV * 0x010FF7B0 (
0x01111520)
with_subscribe_ 618-c6c ENTER SQLAllocConnect
HENV 01111520
HDBC * 01220890
with_subscribe_ 618-c6c EXIT SQLAllocConnect with return
code 0 (SQL_SUCCESS)
HENV 01111520
HDBC * 0x01220890 (
0x011115c8)
with_subscribe_ 618-c6c ENTER SQLConnectW
HDBC 011115C8
WCHAR * 0x011116D0 [
3] "gms"
SWORD 3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
with_subscribe_ 618-c6c EXIT SQLConnectW with return
code 1 (SQL_SUCCESS_WITH_INFO)
HDBC 011115C8
WCHAR * 0x011116D0 [
3] "gms"
SWORD 3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
DIAG [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Changed database context
to 'GasManager'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Changed language setting to us_english.
(5703)
with_subscribe_ 618-c6c ENTER SQLGetInfoW
HDBC 011115C8
UWORD 81
<SQL_GETDATA_EXTENSIONS>
PTR 01220898
SWORD 4
SWORD * 0x0026F27E
with_subscribe_ 618-c6c EXIT SQLGetInfoW with return
code 0 (SQL_SUCCESS)
HDBC 011115C8
UWORD 81
<SQL_GETDATA_EXTENSIONS>
PTR 01220898
SWORD 4
SWORD * 0x0026F27E (4)
with_subscribe_ 618-c6c ENTER SQLAllocStmt
HDBC 011115C8
HSTMT * 0026EA7CThe log is an ODBC tracing log so you have ODBC tracing
turned on. To turn it off, go to Administrative Tools and
select Data Sources (ODBC). In the ODBC Data Source
Administrator, go to the tracing tab and click on the Stop
Tracing Now button.
-Sue
On Mon, 15 Mar 2004 15:51:25 -0800, "Branko Kovac"
<branko.kovac@.invensys.com> wrote:
>Hi,
>I am running database on SQL2000 server and sql.log file
>that is associated with the database keeps on growing. I
>would like to reduce its size but don't know how. I would
>appreciate if someone could tell me what is the way to
>reduce the sql.log file size.
>Thanks in advance.
>I did try to backup database, backup log file and shrink
>both database and log file, but these actions did not
>reduce size of sql.log file.
>My database file is:
>D:\pgm_db\OrdersManager_Data.MDF
>My database log file is:
>C:\Program Files\Microsoft SQL
>Server\MSSQL\data\OrdersManager_Log.LDF
>sql.log file is ascii file in the same directory as
>database file. The contents of the file looks like this:
>with_subscribe_ 618-c6c ENTER SQLAllocEnv
> HENV * 010FF7B0
>with_subscribe_ 618-c6c EXIT SQLAllocEnv with return
>code 0 (SQL_SUCCESS)
> HENV * 0x010FF7B0 (
>0x01111520)
>with_subscribe_ 618-c6c ENTER SQLAllocConnect
> HENV 01111520
> HDBC * 01220890
>with_subscribe_ 618-c6c EXIT SQLAllocConnect with return
>code 0 (SQL_SUCCESS)
> HENV 01111520
> HDBC * 0x01220890 (
>0x011115c8)
>with_subscribe_ 618-c6c ENTER SQLConnectW
> HDBC 011115C8
> WCHAR * 0x011116D0 [
>3] "gms"
> SWORD 3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
>with_subscribe_ 618-c6c EXIT SQLConnectW with return
>code 1 (SQL_SUCCESS_WITH_INFO)
> HDBC 011115C8
> WCHAR * 0x011116D0 [
>3] "gms"
> SWORD 3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> DIAG [01000] [Microsoft][ODBC SQL Server
>Driver][SQL Server]Changed database context
>to 'GasManager'. (5701)
> DIAG [01000] [Microsoft][ODBC SQL Server
>Driver][SQL Server]Changed language setting to us_english.
>(5703)
>with_subscribe_ 618-c6c ENTER SQLGetInfoW
> HDBC 011115C8
> UWORD 81
><SQL_GETDATA_EXTENSIONS>
> PTR 01220898
> SWORD 4
> SWORD * 0x0026F27E
>with_subscribe_ 618-c6c EXIT SQLGetInfoW with return
>code 0 (SQL_SUCCESS)
> HDBC 011115C8
> UWORD 81
><SQL_GETDATA_EXTENSIONS>
> PTR 01220898
> SWORD 4
> SWORD * 0x0026F27E (4)
>with_subscribe_ 618-c6c ENTER SQLAllocStmt
> HDBC 011115C8
> HSTMT * 0026EA7C|||Thanks for this Sue. It worked.
>--Original Message--
>The log is an ODBC tracing log so you have ODBC tracing
>turned on. To turn it off, go to Administrative Tools and
>select Data Sources (ODBC). In the ODBC Data Source
>Administrator, go to the tracing tab and click on the Stop
>Tracing Now button.
>-Sue
>On Mon, 15 Mar 2004 15:51:25 -0800, "Branko Kovac"
><branko.kovac@.invensys.com> wrote:
>
would
return
us_english.
>.
>sql
I am running database on SQL2000 server and sql.log file
that is associated with the database keeps on growing. I
would like to reduce its size but don't know how. I would
appreciate if someone could tell me what is the way to
reduce the sql.log file size.
Thanks in advance.
I did try to backup database, backup log file and shrink
both database and log file, but these actions did not
reduce size of sql.log file.
My database file is:
D:\pgm_db\OrdersManager_Data.MDF
My database log file is:
C:\Program Files\Microsoft SQL
Server\MSSQL\data\OrdersManager_Log.LDF
sql.log file is ascii file in the same directory as
database file. The contents of the file looks like this:
with_subscribe_ 618-c6c ENTER SQLAllocEnv
HENV * 010FF7B0
with_subscribe_ 618-c6c EXIT SQLAllocEnv with return
code 0 (SQL_SUCCESS)
HENV * 0x010FF7B0 (
0x01111520)
with_subscribe_ 618-c6c ENTER SQLAllocConnect
HENV 01111520
HDBC * 01220890
with_subscribe_ 618-c6c EXIT SQLAllocConnect with return
code 0 (SQL_SUCCESS)
HENV 01111520
HDBC * 0x01220890 (
0x011115c8)
with_subscribe_ 618-c6c ENTER SQLConnectW
HDBC 011115C8
WCHAR * 0x011116D0 [
3] "gms"
SWORD 3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
with_subscribe_ 618-c6c EXIT SQLConnectW with return
code 1 (SQL_SUCCESS_WITH_INFO)
HDBC 011115C8
WCHAR * 0x011116D0 [
3] "gms"
SWORD 3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88 [ -
3] "******\ 0"
SWORD -3
DIAG [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Changed database context
to 'GasManager'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Changed language setting to us_english.
(5703)
with_subscribe_ 618-c6c ENTER SQLGetInfoW
HDBC 011115C8
UWORD 81
<SQL_GETDATA_EXTENSIONS>
PTR 01220898
SWORD 4
SWORD * 0x0026F27E
with_subscribe_ 618-c6c EXIT SQLGetInfoW with return
code 0 (SQL_SUCCESS)
HDBC 011115C8
UWORD 81
<SQL_GETDATA_EXTENSIONS>
PTR 01220898
SWORD 4
SWORD * 0x0026F27E (4)
with_subscribe_ 618-c6c ENTER SQLAllocStmt
HDBC 011115C8
HSTMT * 0026EA7CThe log is an ODBC tracing log so you have ODBC tracing
turned on. To turn it off, go to Administrative Tools and
select Data Sources (ODBC). In the ODBC Data Source
Administrator, go to the tracing tab and click on the Stop
Tracing Now button.
-Sue
On Mon, 15 Mar 2004 15:51:25 -0800, "Branko Kovac"
<branko.kovac@.invensys.com> wrote:
>Hi,
>I am running database on SQL2000 server and sql.log file
>that is associated with the database keeps on growing. I
>would like to reduce its size but don't know how. I would
>appreciate if someone could tell me what is the way to
>reduce the sql.log file size.
>Thanks in advance.
>I did try to backup database, backup log file and shrink
>both database and log file, but these actions did not
>reduce size of sql.log file.
>My database file is:
>D:\pgm_db\OrdersManager_Data.MDF
>My database log file is:
>C:\Program Files\Microsoft SQL
>Server\MSSQL\data\OrdersManager_Log.LDF
>sql.log file is ascii file in the same directory as
>database file. The contents of the file looks like this:
>with_subscribe_ 618-c6c ENTER SQLAllocEnv
> HENV * 010FF7B0
>with_subscribe_ 618-c6c EXIT SQLAllocEnv with return
>code 0 (SQL_SUCCESS)
> HENV * 0x010FF7B0 (
>0x01111520)
>with_subscribe_ 618-c6c ENTER SQLAllocConnect
> HENV 01111520
> HDBC * 01220890
>with_subscribe_ 618-c6c EXIT SQLAllocConnect with return
>code 0 (SQL_SUCCESS)
> HENV 01111520
> HDBC * 0x01220890 (
>0x011115c8)
>with_subscribe_ 618-c6c ENTER SQLConnectW
> HDBC 011115C8
> WCHAR * 0x011116D0 [
>3] "gms"
> SWORD 3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
>with_subscribe_ 618-c6c EXIT SQLConnectW with return
>code 1 (SQL_SUCCESS_WITH_INFO)
> HDBC 011115C8
> WCHAR * 0x011116D0 [
>3] "gms"
> SWORD 3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7F8B88 [ -
>3] "******\ 0"
> SWORD -3
> DIAG [01000] [Microsoft][ODBC SQL Server
>Driver][SQL Server]Changed database context
>to 'GasManager'. (5701)
> DIAG [01000] [Microsoft][ODBC SQL Server
>Driver][SQL Server]Changed language setting to us_english.
>(5703)
>with_subscribe_ 618-c6c ENTER SQLGetInfoW
> HDBC 011115C8
> UWORD 81
><SQL_GETDATA_EXTENSIONS>
> PTR 01220898
> SWORD 4
> SWORD * 0x0026F27E
>with_subscribe_ 618-c6c EXIT SQLGetInfoW with return
>code 0 (SQL_SUCCESS)
> HDBC 011115C8
> UWORD 81
><SQL_GETDATA_EXTENSIONS>
> PTR 01220898
> SWORD 4
> SWORD * 0x0026F27E (4)
>with_subscribe_ 618-c6c ENTER SQLAllocStmt
> HDBC 011115C8
> HSTMT * 0026EA7C|||Thanks for this Sue. It worked.
>--Original Message--
>The log is an ODBC tracing log so you have ODBC tracing
>turned on. To turn it off, go to Administrative Tools and
>select Data Sources (ODBC). In the ODBC Data Source
>Administrator, go to the tracing tab and click on the Stop
>Tracing Now button.
>-Sue
>On Mon, 15 Mar 2004 15:51:25 -0800, "Branko Kovac"
><branko.kovac@.invensys.com> wrote:
>
would
return
us_english.
>.
>sql
Wednesday, March 21, 2012
How to reduce white space when object is hidden?
The report is made of one list containing 2 tables, one below each other.
Depending on the report parameters, the bottom table is made visible or
hidden.
When that table is hidden at run time, RS still reserves the same amount of
white space that the table takes in the report design layout.
This causes RS to render an extra white page at the end of the report where
it would display the table. Since the table is hidden, nothing is displayed
in this extra white page.
Does anyone know a workaround that would prevent the white page at the end
of the report?Hi Pat,
Thanks for your post.
From your descriptions, I understood the report will reserve some the
space for that hidden table when there is two tables in the Report. If I
have misunderstood your concern, please feel free to point it out.
I have tested on my side but it seems RS won't reserve the place for hidden
objects. Would you please show me a sample with AdventureWorks, with which
I could reproduce it on my side?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Did you try the scenario where both table are contained in one list?
"Michael Cheng [MSFT]" wrote:
> Hi Pat,
> Thanks for your post.
> From your descriptions, I understood the report will reserve some the
> space for that hidden table when there is two tables in the Report. If I
> have misunderstood your concern, please feel free to point it out.
> I have tested on my side but it seems RS won't reserve the place for hidden
> objects. Would you please show me a sample with AdventureWorks, with which
> I could reproduce it on my side?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Pat,
Thanks for your patience.
If you set the visibility like this:
<Visibility>
<Hidden>true</Hidden>
</Visibility>
This means that the table is always hidden. The space is preserved for
always hidden items. If you changed visibility according to Expression, it
will not reduce the white space.
However, I have tested the scenario that both table are contained in one
list as you described. It will do leave the blank and I am afraid currently
we cannot eliminate this behavior.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Depending on the report parameters, the bottom table is made visible or
hidden.
When that table is hidden at run time, RS still reserves the same amount of
white space that the table takes in the report design layout.
This causes RS to render an extra white page at the end of the report where
it would display the table. Since the table is hidden, nothing is displayed
in this extra white page.
Does anyone know a workaround that would prevent the white page at the end
of the report?Hi Pat,
Thanks for your post.
From your descriptions, I understood the report will reserve some the
space for that hidden table when there is two tables in the Report. If I
have misunderstood your concern, please feel free to point it out.
I have tested on my side but it seems RS won't reserve the place for hidden
objects. Would you please show me a sample with AdventureWorks, with which
I could reproduce it on my side?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Did you try the scenario where both table are contained in one list?
"Michael Cheng [MSFT]" wrote:
> Hi Pat,
> Thanks for your post.
> From your descriptions, I understood the report will reserve some the
> space for that hidden table when there is two tables in the Report. If I
> have misunderstood your concern, please feel free to point it out.
> I have tested on my side but it seems RS won't reserve the place for hidden
> objects. Would you please show me a sample with AdventureWorks, with which
> I could reproduce it on my side?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Pat,
Thanks for your patience.
If you set the visibility like this:
<Visibility>
<Hidden>true</Hidden>
</Visibility>
This means that the table is always hidden. The space is preserved for
always hidden items. If you changed visibility according to Expression, it
will not reduce the white space.
However, I have tested the scenario that both table are contained in one
list as you described. It will do leave the blank and I am afraid currently
we cannot eliminate this behavior.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
How to reduce Transaction log file ?
Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.
First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_s hrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.
First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_s hrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
How to reduce Transaction log file ?
Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple o
f
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncat
e
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize> ) where the TranLogLogicalName is the logica
l
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
up
> everyday, but the Transaction log back up failed because there is not enou
gh
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_
in_mb_to_shrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple o
f
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncat
e
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize> ) where the TranLogLogicalName is the logica
l
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
up
> everyday, but the Transaction log back up failed because there is not enou
gh
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_
in_mb_to_shrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
How to reduce Transaction log file ?
Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_shrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_shrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
How to reduce timeouts in SQL Server?
Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/community/chats/trans/sql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregonsql
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/community/chats/trans/sql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregonsql
How to reduce timeouts in SQL Server?
Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karan
ouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon
|||Articles for reference:
http://www.microsoft.com/technet/com...l/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>
|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan
|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karan
ouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon
|||Articles for reference:
http://www.microsoft.com/technet/com...l/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>
|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan
|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon
How to reduce timeouts in SQL Server?
Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/co...ql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599
]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/co...ql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599
]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon
How to reduce the unallocated space of a database?
I tried DBCC SHRINKDATABASE, however, can not reduce the unallocated space. Please help!Press rite click on a database u want n then goto all tasks>shrink database
press files button
select database file u want to shrink
then select shrink file to (type used space)
press ok button|||You can also set the database to auto_shrink. Enterprise manager: right-click on your database, choose properties and then the options tabl. You'll see just below the middle on the right the option auto_shrink.
press files button
select database file u want to shrink
then select shrink file to (type used space)
press ok button|||You can also set the database to auto_shrink. Enterprise manager: right-click on your database, choose properties and then the options tabl. You'll see just below the middle on the right the option auto_shrink.
How to reduce the transaction log file
Hi all,
I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me
database size created with automatically growth of 10% with unrestricted file growth.
database size = 5 Gb used 4.5 GB (taskpad)
transaction log=8GB used 54MB (taskpad) 7.5 GB free
database run in FULL mode
full backup nightly, transaction log backup every 30 min
What should I do to free up the space that are not used in the transaction log.
Thanks for your help.DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)|||How big is your hard drive?
If it's like 90GB...don't worry about it...
I'd be more worried though about unrestricted growth...
but you maint plan seems pretty good, and 7 gb for a tranny I would doubt would blow out...|||Doesn't your half hourly transaction log backup clear the space automatically?|||Originally posted by suresh_m_kumar
Doesn't your half hourly transaction log backup clear the space automatically?
No. that is why I don't understand|||The tranny backup every 30 min will truncate the log, but transaction log file (*ldf) is not automatically shrunk witht he truncation - see the post above with the syntax for DBCC shrink file - once you do this, the 30 min backups should keep the log to a more manageable size.|||Originally posted by joejcheng
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
What option should I take : Truncateonly ??
[ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY }|||You will want to use truncateonly I belive, so the freed space will be allocated back to the OS. Read this for an explanation of the options, or use the BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me
database size created with automatically growth of 10% with unrestricted file growth.
database size = 5 Gb used 4.5 GB (taskpad)
transaction log=8GB used 54MB (taskpad) 7.5 GB free
database run in FULL mode
full backup nightly, transaction log backup every 30 min
What should I do to free up the space that are not used in the transaction log.
Thanks for your help.DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)|||How big is your hard drive?
If it's like 90GB...don't worry about it...
I'd be more worried though about unrestricted growth...
but you maint plan seems pretty good, and 7 gb for a tranny I would doubt would blow out...|||Doesn't your half hourly transaction log backup clear the space automatically?|||Originally posted by suresh_m_kumar
Doesn't your half hourly transaction log backup clear the space automatically?
No. that is why I don't understand|||The tranny backup every 30 min will truncate the log, but transaction log file (*ldf) is not automatically shrunk witht he truncation - see the post above with the syntax for DBCC shrink file - once you do this, the 30 min backups should keep the log to a more manageable size.|||Originally posted by joejcheng
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
What option should I take : Truncateonly ??
[ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY }|||You will want to use truncateonly I belive, so the freed space will be allocated back to the OS. Read this for an explanation of the options, or use the BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
How to reduce the time when using a variable
Hi,
I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @.i and now my select statement is like this: 'Select substring(column_name,@.i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.
Why it is like this? Is there any way I can reduce the time taken to execute each iteration?
Thanks.
how many records of this kind are you processing.
the number of records must ne put into consideration.
a cursor that iterate with every records first must be put in place
then place the while loop inside every fetch of the cursor
do the processing(while loop) of the column
i think there should be two loops to handle this case.
one that iterates with the records
and one that iterates for every codes in the the column.
if this is an SP. a with recompile option will make your query faster
regards.
|||What about
INSERT INTO table1
SELECT SUBSTRING(column_name,1,3) from table WHERE SUBSTRING(column_name,1,3) <> ''
UNION
SELECT SUBSTRING(column_name,4,3) from table WHERE SUBSTRING(column_name,4,3) <> ''
UNION
...
? Copy/paste above line until substring index matches column_name size; or, if you don't want to hardcode it - construct this query referring to column_name size and run it through EXEC.
This could be faster - try it out...
|||If you are using SQL Server 2005 then you can do the following:
1. Create a table-valued function that takes a string and splits it based on space character. You can search the web for several sample functions that does this operation
2. Use it in a SELECT statement using APPLY operator like:
insert into tbl(i, ...)
select s.val
from table as t
cross apply splitstr(t.column_name) as s
In SQL Server 2000, you can do similar operation except there is no APPLY operator so you cannot use a TVF. You can use a table of numbers to split the string and then do a cross join with this table instead. Or the approach using UNION as suggested in another post will also work. Make sure you use UNION ALL though for better performance and to eliminate the unnecessary sorting.
|||Here is an article on handling arrays and lists - quite a few methods and instruments, may be useful.|||Hi Umachandar,
Your tip was very helpful to me. Now the whole process completes in 2 mins. Thx.
sqlHow to reduce the size of transaction log
Hello,
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo Sicouret
The following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo Sicouret
The following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
How to reduce the size of transaction log
Hello,
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo SicouretThe following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo SicouretThe following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
How to reduce the size of transaction log
Hello,
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo SicouretThe following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
Please could you send me the neccesary steps to reduce the size of the
transaction log in a database, without taking the database offline?
Thanks in advance.
Eduardo SicouretThe following Microsoft KB article should help you out:
http://support.microsoft.com/kb/272318/en-us
- Peter Ward
WARDY IT solutions
"Eduardo Sicouret" wrote:
> Hello,
> Please could you send me the neccesary steps to reduce the size of the
> transaction log in a database, without taking the database offline?
> Thanks in advance.
> Eduardo Sicouret
>
>
how to reduce the size of transaction file
i've run "dbcc shrinkfile ('employ')"
The size of the file does not reduced.Win
It could be because the LOG file has an actve portions of transaction. Did
you run BACKUP LOG prior a SHRINKING?
select DB_ID('dbname')
Run DBCC LOGINFO(7)
If you see at the bottom transactions with status=2 that means SQL Server
'needs' them and you could not shrink.
However you can run dump inserting to moce those transaction at the
beginning of the LOG
"Win" <aaa@.aaa.com> wrote in message
news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>|||You'll find some information about why it might not shrink in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Win" <aaa@.aaa.com> wrote in message news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...arkred">
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>
The size of the file does not reduced.Win
It could be because the LOG file has an actve portions of transaction. Did
you run BACKUP LOG prior a SHRINKING?
select DB_ID('dbname')
Run DBCC LOGINFO(7)
If you see at the bottom transactions with status=2 that means SQL Server
'needs' them and you could not shrink.
However you can run dump inserting to moce those transaction at the
beginning of the LOG
"Win" <aaa@.aaa.com> wrote in message
news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>|||You'll find some information about why it might not shrink in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Win" <aaa@.aaa.com> wrote in message news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...arkred">
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>
How to reduce the log file size.
Hi all,
How to reduce the physical file size of Transaction log in
SQL 7.0?>--Original Message--
>Hi all,
>How to reduce the physical file size of Transaction log
in
>SQL 7.0?
>.
>Ytan, this has happened to me. The hard drive on the SQL
box was full.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ytan" <y24042003@.yahoo.com> wrote in message news:0fea01c38887$5bde8ca0$a401280a@.phx.gbl...
> Hi all,
> How to reduce the physical file size of Transaction log in
> SQL 7.0?sql
How to reduce the physical file size of Transaction log in
SQL 7.0?>--Original Message--
>Hi all,
>How to reduce the physical file size of Transaction log
in
>SQL 7.0?
>.
>Ytan, this has happened to me. The hard drive on the SQL
box was full.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ytan" <y24042003@.yahoo.com> wrote in message news:0fea01c38887$5bde8ca0$a401280a@.phx.gbl...
> Hi all,
> How to reduce the physical file size of Transaction log in
> SQL 7.0?sql
How to reduce the log file size
Hi There,
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
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
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
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
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
How to reduce the log file size
Hi There,
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***
In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
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
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***
In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
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
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
Subscribe to:
Posts (Atom)