Newbie question on physical file last mod date vs. virtual DB/Log

I think I understand the basic features of log files and how to
backup/truncate them to avoid oversized files. Where I need
help is understanding why the O/S rarely puts a new datetime
on the physical files. It appears to only update the log file
time when the log file grows. The DB file may go a month or more
without updating the timestamp. Our backups of the DB grow in
size and appear to be OK. CHECKPOINT commands don't force
the physical disk to update. Is there any other way to do it
(short of drastic measures like detaching the DB)?
Here's my concern: Suppose the server crashes due to a power
failure/UPS failure/whatever. When it restarts it will look at the
log to recover the DB. The physical DB file appears to be a month old
and the log datetime appears to be a day or two old.
If I truncated my log at any time in the last month then it seems like
it will not be able to recover correctly.
Is this really a problem or is it OK?
Environment: SQL Server 2000 on Win2K ServerWhy and when Windows updates the datetime for the file, I don't know. Perhaps somebody in the
windows forum can answer that. However:
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB.
Correct. SQL Server know where to find the ldf file, it is stored both in the mdf file as well as in
the master database.
> The physical DB file appears to be a month old
Doesn't matter to SQL Server.
> and the log datetime appears to be a day or two old.
Can you explain what you mean by "log datetime"?
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
What do you mean by "truncated"? Something like BACKUP LOG ... WITH TRUNCATE_ONLY? As long as you
haven't deleted the log file and replaced with an older version, you are fine. SQL server will not
remove log records needed to do recovery of the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:0817C2B3-1F18-4B50-A4C8-6A54D52D0D87@.microsoft.com...
>I think I understand the basic features of log files and how to
> backup/truncate them to avoid oversized files. Where I need
> help is understanding why the O/S rarely puts a new datetime
> on the physical files. It appears to only update the log file
> time when the log file grows. The DB file may go a month or more
> without updating the timestamp. Our backups of the DB grow in
> size and appear to be OK. CHECKPOINT commands don't force
> the physical disk to update. Is there any other way to do it
> (short of drastic measures like detaching the DB)?
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB. The physical DB file appears to be a month old
> and the log datetime appears to be a day or two old.
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
> Is this really a problem or is it OK?
> Environment: SQL Server 2000 on Win2K Server
>|||Re: Can you explain what you mean by "log datetime"?
Answer: The last modification time on the log file (i.e, both
the DB file and the Log file appear to be "old" on the disk).
Re: What do you mean by "truncated"?
Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
Answer: Yes.
If the log file had a recent last modification time (the Windows File)
then it all makes sense. It seems like "magic" because it "looks" like
SQL Server does recovery with an very old DB file and a not-so-recent
Log file. Is it possible the disk files are actually updated by SQL
Server without changing the disk file last modification time?
Thank you for your help.|||> Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
Yes, this is what is happening. Again, check with the Windows people under what conditions the NTFS
file timestamps are changed. I understand that you find this ... interesting, but just don't worry
about the file timestamps.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:898EB1A4-A02E-456F-B82A-4391608EAD6A@.microsoft.com...
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>|||Don Anthony wrote:
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>
I'm not sure, but I'd think that the file timestamp for the logfile and
database file are only updated when the files are actually changed. That
could e.g. be when the file grows or shrinks.
I've just looked at one of our databases, and here the date for the
database file is 26. febr. 2006 and for the logfile it's 3. jan. 2006.
Like Tibors says, SQL server isn't using these file timestamps for
anything, so help yourself and don't worry about them...:-).
Regards
Steen

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.
Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:

> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.
|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/Transacti...leGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?
|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:

> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/Transacti...leGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?
|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.

> How can I check which recovery mode is it in.
sp_helpdb
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...[vbcol=seagreen]
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:
> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:
> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.
> How can I check which recovery mode is it in.
sp_helpdb
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:
> > If you are talking about the database free space in enterprise manager then
> > that is data and log so it would probably be the logs that are causing the
> > problem.
> > If the database is in full recovery mode then transaction logs will fill up
> > until truncated or baced up. This does not happen until after the first
> > backup though (log is automatically truncated until then as the backups are
> > useless without a full backup). Maybe your truncate is causing the same
> > effect and you would start using more space after the next full backup.
> >
> > Have a look at
> > http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> >
> > If it's to do with just data then it's probably fragmentation or the way you
> > are checking the file space.
> > 30 - 90 sounds a lot though.
> > How is the audit trail taken? Do you have something that depends on the
> > previous data?

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
142409647263409647256
Message posted via http://www.sqlmonster.com
Can you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_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
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
--
Message posted via http://www.sqlmonster.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_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
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
Message posted via http://www.droptable.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_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
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com