Log of SQL

Good Afternoon,
I Want to consult if is possible read the Log File (LDF), because I Need to
Know What movements have had the DataBase is diferents Tables.
Thak for you help
Felipe Bolvar RestrepoI don't think it is easy to understand using DBCC LOG, otherwise third party
tools such as APexSQL or Lumigent's Log explorer will help you with suitable
format.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Luis Felipe Bol_var Restrepo" wrote:

> Good Afternoon,
> I Want to consult if is possible read the Log File (LDF), because I Need t
o
> Know What movements have had the DataBase is diferents Tables.
> Thak for you help
>
> Felipe Bol_var Restrepo
>
>

Log of SQL

Good Afternoon,
I Want to consult if is possible read the Log File (LDF), because I Need to
Know What movements have had the DataBase is diferents Tables.
Thak for you help
Felipe Bolvar Restrepo
I don't think it is easy to understand using DBCC LOG, otherwise third party
tools such as APexSQL or Lumigent's Log explorer will help you with suitable
format.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Luis Felipe BolXvar Restrepo" wrote:

> Good Afternoon,
> I Want to consult if is possible read the Log File (LDF), because I Need to
> Know What movements have had the DataBase is diferents Tables.
> Thak for you help
>
> Felipe BolXvar Restrepo
>
>

Log files must be named *_log.ldf?

Hello,

I've been having problems with SQL Express when my database files are named basename.mdf and basename.ldf (e.g. on using the Access upsizing wizard).

At points that I haven't fully identified yet (I'm using VB Express clients), a second log file named basename_log.mdf gets created and SQL Express gets confused and compalins the files are corrupted.

Can anyone explain what is happening?

TIA,

Bill

SQL Server files do not really need to have a specific extension, so it might be possible that this file, although having a ldf extension is not a ldf file. Which files did the wizard create for you ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hello Jens.

My problem is not with the ldf extension but with a second transaction log file getting created.

As an example:

1) Create a new Access database namedmyDatabase.mdb (containing a single table.)

2) Use the Upsizing wizard to create a new SQL database from the Access database accepting the defaults as much as possible. This creates files named myDatabaseSQL.mdf and myDatabaseSQL.ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

3) The database myDatabaseSql is already visible when you next open up MSS MSE, from where you can see the data in the one table.

4) So far everything is ok.

5) Now exit MSS MSE and start VB 2k5 Express. Without opening up any project or solution, simply open the Database Explorer and create a new connection to myDatabaseSQL. Hit the Test Connection button and once told it’s ok, immediately exit VB.

6) At this point there are now three files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data :

myDatabaseSQL.mdf

myDatabaseSQL.ldf

myDataBaseSQL_log.ldf (this one seems to be created by VB's Database Explorer)

7) Now open up MSS MSE again and try to get to table within myDatabaseSQL from the object explorer. On clicking on myDatabaseSQL you get the following error message

One or more files do not match the primary file of the database….. If this is an existing database the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error 5173)

This behaviour is repeated on all my databases including Adventure Works and NorthWind.

I got the problem on the ‘smaller ‘ SQL Server Express + MSS MSE down loads on one machine and also get the problem with the ‘larger’ SSE with Advanced Services + Toolkit downloads on a second machine.

Both machines are running XP Pro + Office 2003 + 2005 Express versions of SS + VB.

Hope this lot help.

TIA

Bill

sql

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.orgHi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?
> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.orgHi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?

> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
http://www.aspfaq.com/
(Reverse address to reply.)sql

log files

how can i read a the log file of my database .ldf
*** Sent via Developersdex http://www.examnotes.net ***You have programs for that:
try ang Lumigent Log Explorer for SQL Server
http://www.lumigent.com/products/le_sql_faq.html
or the apex tools
http://www.apexsql.com/sql_tools_log.asp
lp,S
"Roy Aouad" <royaouad@.gmail.com> wrote in message
news:O400HAZnFHA.4056@.TK2MSFTNGP10.phx.gbl...
>
> how can i read a the log file of my database .ldf
> *** Sent via Developersdex http://www.examnotes.net ***

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.org
Hi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>
|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?

> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
http://www.aspfaq.com/
(Reverse address to reply.)

Log File too big

The size of the .MDF is 175,000 Kb while that of the .LDF is 2,377,000 Kb. What should I do to truncate the Log File? Pls advise.before executing this code read BOL, dbcc shrinkfile

backup DATABASE yourdatabasename to disk='c:\yourdatabasename .bak'
go
dbcc shrinkfile (yourdatabase_log,size,truncateonly)
go
--to get log file name--
select * from sysfiles|||1.Please change the db options to Simple recovery Model.
2.Use the DBCC Shrinkfile ('FileNAme','SizeinMB) e.g DBCC Shrinkfile ('Myfile',100)

Thankssql

Log file size

Hi,
Can anyone help on this, how to reduce the size of the log file ?
I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
17Gbytes. The database has been in operation for more than 2 years and why
has the log file grown to such a size ? It was set to auto growth at 10%. Can
I reduce it size and how? I need to claim back some disk space.
Thank you for your time
KC ThongKCThong wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf
> file is 17Gbytes. The database has been in operation for more than 2
> years and why has the log file grown to such a size ? It was set to
> auto growth at 10%. Can I reduce it size and how? I need to claim
> back some disk space.
> Thank you for your time
> KC Thong
See DBCC SHRINKFILE in BOL.
--
David Gugick
Imceda Software
www.imceda.com|||Here are a few things to keep in mind:
â?¢ Always perform system database and user database backups before and after
you make changes that affect the system. DBCC SHRINKFILE and DBCC
SHRINKDATABASE are not logged operations, and running them invalidates
further transaction log backups. You must make a full database backup after
you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
â?¢ Make sure that there are no backups scheduled to occur during the time the
shrink is supposed to occur.
â?¢ Make sure that there are no old, long-running, or unreplicated
transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
members of the sysadmin fixed server role or the db_owner fixed database
role, and are not transferable. For information about the differences between
these commands, refer to the following topics in SQL Books Online (note the
different parameters):
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)
â?¢ Create some dummy transactions to make the log wrap around and then issue
a BACKUP command to truncate the log. The BACKUP statement is what actually
attempts to shrink the log to the marked target size.
Here is a sample of how to create a dummy transactions that wraps the log
for a single logical log file and causes it to truncate, allowing for
shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Check to see if the log has shrunk from its original size.Repeat the
preceding steps if necessary. If the log is not shrinking, re-check the
summary at the top of the article to see if you are encountering any of the
common issues with shrinking the log.
After the log shrinks:
1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary
because the SHRINK command is not logged and invalidates future transaction
log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check
for open transactions, long running transactions, unreplicated transactions,
or transactions that touch a lot of data.
"KCThong" wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> 17Gbytes. The database has been in operation for more than 2 years and why
> has the log file grown to such a size ? It was set to auto growth at 10%. Can
> I reduce it size and how? I need to claim back some disk space.
> Thank you for your time
> KC Thong
>
>|||At this stage, if I set " trunc. log on chkpt" in the database option to
TRUE, does it help to clear the log or shrink ?
Pls advise.
"surajits" wrote:
> Here are a few things to keep in mind:
> â?¢ Always perform system database and user database backups before and after
> you make changes that affect the system. DBCC SHRINKFILE and DBCC
> SHRINKDATABASE are not logged operations, and running them invalidates
> further transaction log backups. You must make a full database backup after
> you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
> â?¢ Make sure that there are no backups scheduled to occur during the time the
> shrink is supposed to occur.
> â?¢ Make sure that there are no old, long-running, or unreplicated
> transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
> â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
> shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
> members of the sysadmin fixed server role or the db_owner fixed database
> role, and are not transferable. For information about the differences between
> these commands, refer to the following topics in SQL Books Online (note the
> different parameters):
> DBCC SHRINKFILE (file_name, target_size)
> DBCC SHRINKDATABASE (database_name, target_percent)
> â?¢ Create some dummy transactions to make the log wrap around and then issue
> a BACKUP command to truncate the log. The BACKUP statement is what actually
> attempts to shrink the log to the marked target size.
> Here is a sample of how to create a dummy transactions that wraps the log
> for a single logical log file and causes it to truncate, allowing for
> shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
> DECLARE @.LogicalFileName sysname,
> @.MaxMinutes INT,
> @.NewSize INT
> -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
> USE [Test DB] -- This is the name of the database
> -- for which the log will be shrunk.
> SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
> -- identify the logical file
> -- name that you want to shrink.
> @.MaxMinutes = 10, -- Limit on time allowed to wrap log.
> @.NewSize = 10 -- in MB
> -- Setup / initialize
> DECLARE @.OriginalSize int
> SELECT @.OriginalSize = size -- in 8K pages
> FROM sysfiles
> WHERE name = @.LogicalFileName
> SELECT 'Original Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> CREATE TABLE DummyTrans
> (DummyColumn char (8000) not null)
> -- Wrap log and truncate it.
> DECLARE @.Counter INT,
> @.StartTime DATETIME,
> @.TruncLog VARCHAR(255)
> SELECT @.StartTime = GETDATE(),
> @.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
> -- Try an initial shrink.
> DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
> EXEC (@.TruncLog)
> -- Wrap the log if necessary.
> WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
> not expired
> AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name => @.LogicalFileName) -- the log has not shrunk
> AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
> for new size is smaller than the current size.
> BEGIN -- Outer loop.
> SELECT @.Counter = 0
> WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
> BEGIN -- update
> INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
> field it inserts 8000 bytes.
> DELETE DummyTrans
> SELECT @.Counter = @.Counter + 1
> END -- update
> EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
> END -- outer loop
> SELECT 'Final Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> DROP TABLE DummyTrans
> PRINT '*** Perform a full database backup ***'
> SET NOCOUNT OFF
> Check to see if the log has shrunk from its original size.Repeat the
> preceding steps if necessary. If the log is not shrinking, re-check the
> summary at the top of the article to see if you are encountering any of the
> common issues with shrinking the log.
> After the log shrinks:
> 1. Perform a full database backup of the master database.
> 2. Perform a full database backup of the user database. This is necessary
> because the SHRINK command is not logged and invalidates future transaction
> log backups unless a full database backup is completed.
> To determine why the log is growing so big in the first place, you can check
> for open transactions, long running transactions, unreplicated transactions,
> or transactions that touch a lot of data.
> "KCThong" wrote:
> > Hi,
> > Can anyone help on this, how to reduce the size of the log file ?
> > I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> > 17Gbytes. The database has been in operation for more than 2 years and why
> > has the log file grown to such a size ? It was set to auto growth at 10%. Can
> > I reduce it size and how? I need to claim back some disk space.
> >
> > Thank you for your time
> > KC Thong
> >
> >
> >
> >sql

log file size

Dear Sir
I found that my database file (*.mdf) size is about
600 mb, but my log file (*.ldf) size is about 16 GB. My
harddisk has not enough space to place this file, if the
log continues to increase. I want to ask does any method
too reduced the size of the log file, becuase the log file
is not important for us'
Thank youIf you don't backup your transaction log regularly as part of your
recovery plan, you can have SQL Server automatically remove committed
data from the log by setting the recovery model to SIMPLE (SQL 2000) or
turn on the 'trunc. log on chkpt.' database option (SQL 7). Note that
your only recovery option in the SIMPLE recovery model is to restore
from full and differential backup.
For example:
SQL 2000:
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
SQL 7:
EXEC sp_dboption 'MyDatabase', 'trunc. log on chkpt.', true
To reduce the size of your log, use DBCC SHRINKFILE. The example below
will shrink the log file to 200MB:
USE MyDatabase
DBCC SHRINKFILE('MyDatabase_Log', 200)
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"back1" <back1@.mail.hongkong.com> wrote in message
news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
> Dear Sir
> I found that my database file (*.mdf) size is about
> 600 mb, but my log file (*.ldf) size is about 16 GB. My
> harddisk has not enough space to place this file, if the
> log continues to increase. I want to ask does any method
> too reduced the size of the log file, becuase the log file
> is not important for us'
> Thank you|||Dear Sir
Thank you for your quickly reply!
I already use this statement "DBCC SHRINKFILE
('MyDatabase_Log', 200)" to shrink the log file, but it
has not any effect. What can i do now '
Thank you
>--Original Message--
>If you don't backup your transaction log regularly as
part of your
>recovery plan, you can have SQL Server automatically
remove committed
>data from the log by setting the recovery model to SIMPLE
(SQL 2000) or
>turn on the 'trunc. log on chkpt.' database option (SQL
7). Note that
>your only recovery option in the SIMPLE recovery model is
to restore
>from full and differential backup.
>For example:
>SQL 2000:
> ALTER DATABASE MyDatabase
> SET RECOVERY SIMPLE
>SQL 7:
> EXEC sp_dboption 'MyDatabase', 'trunc. log on
chkpt.', true
>To reduce the size of your log, use DBCC SHRINKFILE. The
example below
>will shrink the log file to 200MB:
> USE MyDatabase
> DBCC SHRINKFILE('MyDatabase_Log', 200)
>See the Books Online for details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"back1" <back1@.mail.hongkong.com> wrote in message
>news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
>> Dear Sir
>> I found that my database file (*.mdf) size is about
>> 600 mb, but my log file (*.ldf) size is about 16 GB. My
>> harddisk has not enough space to place this file, if the
>> log continues to increase. I want to ask does any method
>> too reduced the size of the log file, becuase the log
file
>> is not important for us'
>> Thank you
>
>.
>

Log file size

Hi,
Can anyone help on this, how to reduce the size of the log file ?
I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
17Gbytes. The database has been in operation for more than 2 years and why
has the log file grown to such a size ? It was set to auto growth at 10%. Can
I reduce it size and how? I need to claim back some disk space.
Thank you for your time
KC Thong
KCThong wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf
> file is 17Gbytes. The database has been in operation for more than 2
> years and why has the log file grown to such a size ? It was set to
> auto growth at 10%. Can I reduce it size and how? I need to claim
> back some disk space.
> Thank you for your time
> KC Thong
See DBCC SHRINKFILE in BOL.
David Gugick
Imceda Software
www.imceda.com
|||Here are a few things to keep in mind:
? Always perform system database and user database backups before and after
you make changes that affect the system. DBCC SHRINKFILE and DBCC
SHRINKDATABASE are not logged operations, and running them invalidates
further transaction log backups. You must make a full database backup after
you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
? Make sure that there are no backups scheduled to occur during the time the
shrink is supposed to occur.
? Make sure that there are no old, long-running, or unreplicated
transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
? Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
members of the sysadmin fixed server role or the db_owner fixed database
role, and are not transferable. For information about the differences between
these commands, refer to the following topics in SQL Books Online (note the
different parameters):
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)
? Create some dummy transactions to make the log wrap around and then issue
a BACKUP command to truncate the log. The BACKUP statement is what actually
attempts to shrink the log to the marked target size.
Here is a sample of how to create a dummy transactions that wraps the log
for a single logical log file and causes it to truncate, allowing for
shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Check to see if the log has shrunk from its original size.Repeat the
preceding steps if necessary. If the log is not shrinking, re-check the
summary at the top of the article to see if you are encountering any of the
common issues with shrinking the log.
After the log shrinks:
1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary
because the SHRINK command is not logged and invalidates future transaction
log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check
for open transactions, long running transactions, unreplicated transactions,
or transactions that touch a lot of data.
"KCThong" wrote:

> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> 17Gbytes. The database has been in operation for more than 2 years and why
> has the log file grown to such a size ? It was set to auto growth at 10%. Can
> I reduce it size and how? I need to claim back some disk space.
> Thank you for your time
> KC Thong
>
>
|||At this stage, if I set " trunc. log on chkpt" in the database option to
TRUE, does it help to clear the log or shrink ?
Pls advise.
"surajits" wrote:
[vbcol=seagreen]
> Here are a few things to keep in mind:
> ? Always perform system database and user database backups before and after
> you make changes that affect the system. DBCC SHRINKFILE and DBCC
> SHRINKDATABASE are not logged operations, and running them invalidates
> further transaction log backups. You must make a full database backup after
> you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
> ? Make sure that there are no backups scheduled to occur during the time the
> shrink is supposed to occur.
> ? Make sure that there are no old, long-running, or unreplicated
> transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
> ? Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
> shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
> members of the sysadmin fixed server role or the db_owner fixed database
> role, and are not transferable. For information about the differences between
> these commands, refer to the following topics in SQL Books Online (note the
> different parameters):
> DBCC SHRINKFILE (file_name, target_size)
> DBCC SHRINKDATABASE (database_name, target_percent)
> ? Create some dummy transactions to make the log wrap around and then issue
> a BACKUP command to truncate the log. The BACKUP statement is what actually
> attempts to shrink the log to the marked target size.
> Here is a sample of how to create a dummy transactions that wraps the log
> for a single logical log file and causes it to truncate, allowing for
> shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
> DECLARE @.LogicalFileName sysname,
> @.MaxMinutes INT,
> @.NewSize INT
> -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
> USE [Test DB] -- This is the name of the database
> -- for which the log will be shrunk.
> SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
> -- identify the logical file
> -- name that you want to shrink.
> @.MaxMinutes = 10, -- Limit on time allowed to wrap log.
> @.NewSize = 10 -- in MB
> -- Setup / initialize
> DECLARE @.OriginalSize int
> SELECT @.OriginalSize = size -- in 8K pages
> FROM sysfiles
> WHERE name = @.LogicalFileName
> SELECT 'Original Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> CREATE TABLE DummyTrans
> (DummyColumn char (8000) not null)
> -- Wrap log and truncate it.
> DECLARE @.Counter INT,
> @.StartTime DATETIME,
> @.TruncLog VARCHAR(255)
> SELECT @.StartTime = GETDATE(),
> @.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
> -- Try an initial shrink.
> DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
> EXEC (@.TruncLog)
> -- Wrap the log if necessary.
> WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
> not expired
> AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
> @.LogicalFileName) -- the log has not shrunk
> AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
> for new size is smaller than the current size.
> BEGIN -- Outer loop.
> SELECT @.Counter = 0
> WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
> BEGIN -- update
> INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
> field it inserts 8000 bytes.
> DELETE DummyTrans
> SELECT @.Counter = @.Counter + 1
> END -- update
> EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
> END -- outer loop
> SELECT 'Final Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> DROP TABLE DummyTrans
> PRINT '*** Perform a full database backup ***'
> SET NOCOUNT OFF
> Check to see if the log has shrunk from its original size.Repeat the
> preceding steps if necessary. If the log is not shrinking, re-check the
> summary at the top of the article to see if you are encountering any of the
> common issues with shrinking the log.
> After the log shrinks:
> 1. Perform a full database backup of the master database.
> 2. Perform a full database backup of the user database. This is necessary
> because the SHRINK command is not logged and invalidates future transaction
> log backups unless a full database backup is completed.
> To determine why the log is growing so big in the first place, you can check
> for open transactions, long running transactions, unreplicated transactions,
> or transactions that touch a lot of data.
> "KCThong" wrote:

Log file of sql server 2000 is too big, how to shrink or clear it?

Hi,
My DB file (.ldf) is only 3 Mb, while the log file's size is almost 30 Mb,
how can I shrink or clear this log file to make it small?
Thanks!30mb is not really big, to adjust size do the following.
Open up Enterprise Manager scroll down to the database in question.
Right click db - click on properties - choose the option tab.
In the recovery panel make sure it's set to Simple rather than Full.
Apply changes and exit db properties.
Then you can now manually shrink the database via the All Tasks menu.
Have fun|||Thank u very much, it works as I expected!
"Greentko" <greentko@.hotmail.com> wrote in message
news:eFK2h19sDHA.3236@.tk2msftngp13.phx.gbl...
> 30mb is not really big, to adjust size do the following.
> Open up Enterprise Manager scroll down to the database in question.
> Right click db - click on properties - choose the option tab.
> In the recovery panel make sure it's set to Simple rather than Full.
> Apply changes and exit db properties.
> Then you can now manually shrink the database via the All Tasks menu.
> Have fun
>|||As Greenko say, 30 MB is nothing. It might just grow back the same size next time you do some batch
operation. However:
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
"VVIPI" <vvipi@.hotmail.com> wrote in message news:uUwMbW8sDHA.628@.tk2msftngp13.phx.gbl...
> Hi,
> My DB file (.ldf) is only 3 Mb, while the log file's size is almost 30 Mb,
> how can I shrink or clear this log file to make it small?
> Thanks!
>
>

Log file is too large

Hi
I want to clean up my log file (LDF extension) cause is ocupying much space
from the computer server. I'm using 2000 version and can't erase it using
DELETE
button from properties of the database.
What should I do? Is that possible?
Thanks in advance
Hi,
Backup the trasnaction log and shrink the transaction log file.
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
SQL Server MVP
"Katty" <Katty@.discussions.microsoft.com> wrote in message
news:A366CB5F-DF6E-4E16-B374-D452C2599C1C@.microsoft.com...
> Hi
> I want to clean up my log file (LDF extension) cause is ocupying much
> space
> from the computer server. I'm using 2000 version and can't erase it using
> DELETE
> button from properties of the database.
> What should I do? Is that possible?
> Thanks in advance
sql

Log file is too large

Hi
I want to clean up my log file (LDF extension) cause is ocupying much space
from the computer server. I'm using 2000 version and can't erase it using
DELETE
button from properties of the database.
What should I do? Is that possible?
Thanks in advanceHi,
Backup the trasnaction log and shrink the transaction log file.
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
SQL Server MVP
"Katty" <Katty@.discussions.microsoft.com> wrote in message
news:A366CB5F-DF6E-4E16-B374-D452C2599C1C@.microsoft.com...
> Hi
> I want to clean up my log file (LDF extension) cause is ocupying much
> space
> from the computer server. I'm using 2000 version and can't erase it using
> DELETE
> button from properties of the database.
> What should I do? Is that possible?
> Thanks in advance

Log file is too large

Hi
I want to clean up my log file (LDF extension) cause is ocupying much space
from the computer server. I'm using 2000 version and can't erase it using
DELETE
button from properties of the database.
What should I do? Is that possible?
Thanks in advanceFirstly, I'd do a backup or truncate the log. Then I might
do a shrinkfile to make it smaller
Vinnie
>--Original Message--
>Hi
>I want to clean up my log file (LDF extension) cause is
ocupying much space
>from the computer server. I'm using 2000 version and
can't erase it using
>DELETE
>button from properties of the database.
>What should I do? Is that possible?
>Thanks in advance
>.
>|||Hi,
Backup the trasnaction log and shrink the transaction log file.
Have a look into the below article,
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.support.microsoft.com/?id=315512
Thanks
Hari
SQL Server MVP
"Katty" <Katty@.discussions.microsoft.com> wrote in message
news:A366CB5F-DF6E-4E16-B374-D452C2599C1C@.microsoft.com...
> Hi
> I want to clean up my log file (LDF extension) cause is ocupying much
> space
> from the computer server. I'm using 2000 version and can't erase it using
> DELETE
> button from properties of the database.
> What should I do? Is that possible?
> Thanks in advance

Log File (LDF) ?? (HELP)

Is there any way to clear out the LDF file or reduce in size?
thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.com> wrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...
> Is there any way to clear out the LDF file or reduce in size?
> thank you in advance fro any help,
> Michael Kintner
>|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.com> wrote:
>Is there any way to clear out the LDF file or reduce in size?
>thank you in advance fro any help,
>Michael Kintner
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.
Roy Harvey
Beacon Falls, CT|||In article <hn50e39j2sjnksnka0m5jem0qq12483ep0@.4ax.com>,
roy_harvey@.snet.net says...
> On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
> <nospam@.nospam.com> wrote:
> >Is there any way to clear out the LDF file or reduce in size?
> >
> >thank you in advance fro any help,
> >Michael Kintner
> First, what recovery model (Simple, Full, Bulk-Logged) is the database
> using? If Simple, it will clear out by itself. For the others it
> will only be cleared with the log is backed up. If no log backups are
> running then it simply grows, and grows, and grows...
> As for reducing the size, DBCC SHRINKFILE will do the job. However
> shrinking the log file should be a VERY rare event. Shrinking the
> file, only to have it grow again, is bad for performance. Allocating
> more space is a significant delay for the transaction that causes it,
> and physical file fragmentation is a common side effect. To over
> simplify things a bit, set the log size large enough and backup the
> log regularly to keep it from getting full.
> Roy Harvey
> Beacon Falls, CT
>
Just to add a bit -- SHRINKFILE can also be thwarted by open long-
running transactions since the shrink is done in units of VLF's and
won't occur if there is an open active transaction within the virtual
log. In order to get back space recently -- a 5G database had an 8G
logfile -- I had to backup the log, truncate the logfile and then shrink
to a more reasonable target. I figured 25% was a good guesstimate and of
that only 14M was actually used. Why so out of whack? -- try running an
app w/ no BACKUP LOG for over 2 years. The vendor never bothered to tell
my predecessor the basics of configuring sql and it just grew unbounded.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Log File (LDF) ?? (HELP)

Is there any way to clear out the LDF file or reduce in size?

thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.comwrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...

Quote:

Originally Posted by

Is there any way to clear out the LDF file or reduce in size?
>
thank you in advance fro any help,
Michael Kintner
>

|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.comwrote:

Quote:

Originally Posted by

>Is there any way to clear out the LDF file or reduce in size?
>
>thank you in advance fro any help,
>Michael Kintner

First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...

As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.

Roy Harvey
Beacon Falls, CT

Log file

My transaction log file (.LDF) has reached 53GB on
a (now full) 80GB hard drive.
Is it safe to delete the log file after a check point
is reached (upon DB shut down, for instance?) And why
does the log file not automatically shrink after check-
points?
Thanks,
Andrew> Is it safe to delete the log file after a check point
> is reached
You can not delete the log file, you can run a backup log dbname with
truncate_only if you do not require point in time recovery, of course, if
you don't require point in time recovery you should set the recovery mode of
the database to simple.
>>And why
> does the log file not automatically shrink after check-
> points?
There are two answers here, one, the data stays in the log even after
checkpoint so you can back it up and have the ability to transactionally
recover. Shrinking is another subject, you can not shrink the actual log
file footprint until it is empty, done by either backing up or truncating
it. Also, if you are doing regular backups of the log and your log file is
growing, it doesn't make sense to keep shrinking it only for it to cause you
a performance hit when it auto-grows again.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5201c3c57f$8bfb3340$a301280a@.phx.gbl...
> My transaction log file (.LDF) has reached 53GB on
> a (now full) 80GB hard drive.
> Is it safe to delete the log file after a check point
> is reached (upon DB shut down, for instance?) And why
> does the log file not automatically shrink after check-
> points?
> Thanks,
> Andrew
>