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 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

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.
>

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.
>

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.
>

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

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 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
>
>

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
>
>

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.
>

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 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

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

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

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

How to Reduce or Delete the Log file... Log file size creating problem

I am using Replication with two of my SQL Server Databases , One Db is Publisher and other one is Subscriber , and data is continously replicating after every 5 mins . Now Publisher Database Log File increased upto 2541 MB and its creating problem while having any transaction or having any operation with DB . It also turned Replication utility Off.

I tried to delete log file by turning SQL Server off , but it makes DB mode SUSPECT and UNACCESSBLE.

Plz help me out to delete or reduce the log file size by maintaining the state of data.

Also let me know wht is the method so that log file can automatically delete its contents when reach to certain limit.

Regards
Imran MalikWhy are risking by deleting the SQL system files.

Use DBCC SHRINKFILE to reduce the Tlog file size, refer to books online for more information.|||You haven't mentioned about the version of SQL used.

If its version 7 then enable 'trunc.log at chkpt.' on the database.
If its version 2K then use SIMPLE RECOVERY model.

For more information again refer BOL.:)|||By changing to the simple recovery model, your only backup mechanism is the full or differential backup, you can no longer perform transaction log backups. If you need to shrink the file, however, and maintain the ability to perform transaction log backups, there is a Stored Procedure I found at SQLServerCentral.com. It's called sp_force_shrink_log and I find it quite useful. You can find it at http://www.sqlservercentral.com/scripts/contributions/26.asp after you register with them.|||Thank You very much Satya and loach for telling me the way to reduce the size , I am using SQL Server 2K .

DBCC SHRINKFILE and sp_force_shrink_log are helpful for reducing file size but for log deletion wht I have done is just deattach the database , remove log file and and attach db again , it refreshed and made DB log again with 1 MB. (I tested this with dummy Dbs.)

and my Databases are also connected through replication utility , so it restricts me to deattach db using in replication , so I have to turned replication off to deattach and attach db . currently I am looking into procedure or way so that without disabling replication make log refresh coz it will take long process to disable and then again follow through steps for having last settings to turn replication ON between 2 databases.

Thanks u All

Regards
Imran|||How about the backups schedule interval for LOG?

How to reduce log file size?

I have a problem about log of transection file. How do I set up automatic to reduce size of log file?Have a look at these resources :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:FC013E7B-E4ED-4922-9B19-1967FA02C6AC@.microsoft.com...
> I have a problem about log of transection file. How do I set up automatic
to reduce size of log file?

how to reduce log file size?

Hi all, i have a db with 200 mb log file size. It was set to unlimited grow,
and now i want to reduce its size to 50 mb. In db properties i restricted
log file size to the current size.
Any input would be very helpfull,
thanks> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
Have you tried to archive (backup) it and shrink it again after that? The
T-SQL statement for this is:
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
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
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>|||Thanks guys, you all helped me a lot,
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>

how to reduce log file size?

Hi all, i have a db with 200 mb log file size. It was set to unlimited grow,
and now i want to reduce its size to 50 mb. In db properties i restricted
log file size to the current size.
Any input would be very helpfull,
thanks> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
Have you tried to archive (backup) it and shrink it again after that? The
T-SQL statement for this is:
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>|||Thanks guys, you all helped me a lot,
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>sql

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
Sheetu
First, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A
|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> from the databse which is unused.
>
>
|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
all
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?