FTP Task failure in SQL Job

I have a SQL IS package, with FTP task. It runs fine when I am running from the client. When deployed it on the server as a SQL Job, it failes with error message

"Unable to connect to FTP server using 'FTP Connection Manager' (Name I used for the FTP connection in IS package)".

I tried to run the .dtsx on the server connecting to Intergration services. And I get same error message with little more details, "Password was not allowed".

With same USER_ID and Password and default port number I am able to connect to the FTP site with no problems.

Do I need to do it differntly making the FTP connection inside the IS package? What's the correct way of doing it ?

satya

When you deploy your package to SQL Server make sure you use Protection Level: "Rely on server storage and roles for access control"

|||Thanks for responding to my questions. I did what you suggessted and am still having the same issue.

Steps I followed:

1. Copy the .dtsx to server location from my local.
2. Imported the package to SQL server location with the suggested options.

Do you think, I am missing any thing still ?|||

Yes.
It gets corrupted when you copy.

You can either:
1. Deploy without copy

or

1. Set protection level to EncryptSensitiveWithPassword
2. Copy
3. Imported the package to SQL server location with options I suggested before.

FTP Task and Temporary Internet Files

I have created a package within SQL Server SSIS which includes an FTP Task, deployed it to our SQL Server (2005 SP1) msdb database and am running this job under SQL Agent on Windows Server 2003. Due to company security requirements this job has to be run under a service account within SQL Agent. The problem with this is that even though a directory is specified within the FTP Task to place any downloaded files into, the files are first written to the TIF (Temporary Internet Files) directory of "Default User" which is on the system drive. Based on corporate standards the system drive (C:) on our servers are only configured with enough space for the OS and other system files. All of the files being transferred are compressed, but some are still well over 1GB in size. The result is that many of our downloads are failing due to the system drive running out of space.

I have attempted to run IE by using "Run As" with the service account credentials, and have changed the location of the TIFs to a different drive, rebooted and verified the settings. When the SQL Agent job was run again, the files were still being written to the "Default User" directory on the system drive. I also created a new template account with the TIFs pointing to a non-system drive and used the User Profiles functionality of System Properties to copy the new template account to "Default User", but still the files are being written to the system drive.

My questions are:

is there a way to stop the FTP Task from using TIF (i.e. just directly write the file to the location specified) is there a best practice around how to setup a service account and have it create a proper user profile that can be managed separate from "Default User" short of specifying during the OS install, is there a way to move the "Default User" profile directory to a different drive

In case anyone else runs into this situation, I thought I would describe what ended up happening.

The page file was moved off of the system drive; otherwise the server was locking up when the system drive ran out or space. Now that the page file is on a separate drive, once the system drive runs completely out of space during a download the FTP Task (or whatever is managing the FTP transfer in the background) just starts writing the file being downloaded to the specified location (apparently it's smart enough to stop caching without crashing once there is no space left). Once the file that filled up the system drive completes downloading then it is removed from the TIF; normally the files aren't removed from the TIF until the entire package finishes execution.

The only downside to this is that the time period between the system drive becoming full and the file download completing, there are difficulties with logging into the machine. The difficulty is that some error messages are displayed and not being able to use your user profile - the Default User profile is used instead.

|||I am experiencing a similar problem. Does anybody out there know how to address this issue please?

Chris Lund, Software Installation Limited, Manchester UKsql

FTP No Files Error

When running FTP Client, and there are no files found on the FTP site and
error is thrown. Is there any way to catch this condition and reset the
error so does not bubble up to the parent package?

I want to catch the event that there are not files on the FTP server to get
so that I can set a variable appropriately, but then I want to branch on the
condition and return to the parent package and not have the parent package
see the error that was thrown.

Any suggestions?

Thanks, Mike

You can not catch this specific error condition.

You can control the execution result to be success, failure, or completion, but this will be the case always, not for specific errors only.

Thanks,
Mohammed.

|||Sorry forgot to mention how to control the execution result. You do that by setting the ForceExecutionResult property to the result you want (Success, Failure, or Completion)|||Thanks for the feedback. In the OnTaskFailed Event Handler, I would then like to be able to explicitly set the ForceExecutionResult = Completion. Is it possible to do this at this point? I have not be able to determine how to program this into my script that runs when this event occurs. Maybe this is just not possible.

My issue is that I have had to set the Max Error Count = 2 for the parent package that calls the FTP process to work past this issue. But then I have not way of determining if the error was due to the files not being there, or a legitimate error like the connection to the server could not be established.

I probably should put this on the wish list for an upcoming release.

Thanks, Mike.

FTP job in I64 machine running Windows2003 and SQL2000(64bits)

I am in the process of moving our old SQLServer to a new I64 server.
Everything has been tested and working fine except there is a scheduled FTP
job task that didn't go through. I am hoping if I need to make changes to
accomodate the new I64 platform.
In the new SQL Server, the job task is the following:
ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
The content FTPScript.txt is as follows:
userid
userpassword
cd unixdatafolder
lcd D:\Data
put FileTransfer.txt
bye
After the job is run, the status shows successful but when I telnet to the
unix server, the file is not there. When I do ftp manually and locally in
the I64 server, it has no problem.
Thank you for your asssistance.
MartinHi Martin
If you change the FTP command for (say) an echo statement do you get output?
If so you may want to add the -d parameter to show the commands and capture
the output into a file to see what has happened. Also agent service account
has access to the source file, you may want to try using a cd command before
starting ftp to remove the need for the lcd subcommand.
John
"Martin" wrote:
> I am in the process of moving our old SQLServer to a new I64 server.
> Everything has been tested and working fine except there is a scheduled FTP
> job task that didn't go through. I am hoping if I need to make changes to
> accomodate the new I64 platform.
> In the new SQL Server, the job task is the following:
> ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> The content FTPScript.txt is as follows:
> userid
> userpassword
> cd unixdatafolder
> lcd D:\Data
> put FileTransfer.txt
> bye
> After the job is run, the status shows successful but when I telnet to the
> unix server, the file is not there. When I do ftp manually and locally in
> the I64 server, it has no problem.
> Thank you for your asssistance.
> Martin|||John,
Thank you. The new 64bits machine did have some 'strange behavior'. I will
try using the echo as recommended and see if it makes the difference. I've
spent last two days trying to resolve this issue and after I sent the
question out, just by a random chance, I've found out that the 64bits Job
schedule won't take the url or mapped network drive. After I moved the file
to the local drive on the 64bits machine instead of using network drive other
than the local in the ftp scheduled job task statement, I got the file in
unix box after running the job.
Hope this information could save others time.
Sincerely,
Martin
"John Bell" wrote:
> Hi Martin
> If you change the FTP command for (say) an echo statement do you get output?
> If so you may want to add the -d parameter to show the commands and capture
> the output into a file to see what has happened. Also agent service account
> has access to the source file, you may want to try using a cd command before
> starting ftp to remove the need for the lcd subcommand.
> John
> "Martin" wrote:
> > I am in the process of moving our old SQLServer to a new I64 server.
> > Everything has been tested and working fine except there is a scheduled FTP
> > job task that didn't go through. I am hoping if I need to make changes to
> > accomodate the new I64 platform.
> >
> > In the new SQL Server, the job task is the following:
> >
> > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> >
> > The content FTPScript.txt is as follows:
> > userid
> > userpassword
> > cd unixdatafolder
> > lcd D:\Data
> > put FileTransfer.txt
> > bye
> >
> > After the job is run, the status shows successful but when I telnet to the
> > unix server, the file is not there. When I do ftp manually and locally in
> > the I64 server, it has no problem.
> >
> > Thank you for your asssistance.
> >
> > Martin|||Hi Martin
You may want to try http://www.sqldts.com/default.aspx?302 and see if it is
more reliable. I don't know if the wininet libraries are compatible with this
for I64.
John
"Martin" wrote:
> John,
> Thank you. The new 64bits machine did have some 'strange behavior'. I will
> try using the echo as recommended and see if it makes the difference. I've
> spent last two days trying to resolve this issue and after I sent the
> question out, just by a random chance, I've found out that the 64bits Job
> schedule won't take the url or mapped network drive. After I moved the file
> to the local drive on the 64bits machine instead of using network drive other
> than the local in the ftp scheduled job task statement, I got the file in
> unix box after running the job.
> Hope this information could save others time.
> Sincerely,
> Martin
> "John Bell" wrote:
> > Hi Martin
> >
> > If you change the FTP command for (say) an echo statement do you get output?
> > If so you may want to add the -d parameter to show the commands and capture
> > the output into a file to see what has happened. Also agent service account
> > has access to the source file, you may want to try using a cd command before
> > starting ftp to remove the need for the lcd subcommand.
> >
> > John
> >
> > "Martin" wrote:
> >
> > > I am in the process of moving our old SQLServer to a new I64 server.
> > > Everything has been tested and working fine except there is a scheduled FTP
> > > job task that didn't go through. I am hoping if I need to make changes to
> > > accomodate the new I64 platform.
> > >
> > > In the new SQL Server, the job task is the following:
> > >
> > > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> > >
> > > The content FTPScript.txt is as follows:
> > > userid
> > > userpassword
> > > cd unixdatafolder
> > > lcd D:\Data
> > > put FileTransfer.txt
> > > bye
> > >
> > > After the job is run, the status shows successful but when I telnet to the
> > > unix server, the file is not there. When I do ftp manually and locally in
> > > the I64 server, it has no problem.
> > >
> > > Thank you for your asssistance.
> > >
> > > Martin

FTP from xp_cmdshell

I have a .bat file that uses ftp -s:scriptname that works from a cmd window
and works from running the .bat file but won't work from xp_cmdshell.
The sql agent proxy is set to a system administrator domain account that is
also an admin on the server. The folder where the files are is shared with
everyone full control. Before FTPing, the files are encrytped using
xp_cmdshell to call and that works fine. I can't figure it out. Can anyone
see what I'm missing? Here is the output from the job with the dashed lines
edited out:
Job 'Test InfoTransfer' : Step 1, 'Encrypt the files' : Began Executing
2006-06-22 15:12:05
output
(null)
C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
\\sqldss01\GNUPG\Transfer\6984Test.gpg -e \\sqldss01\c$\PGArchive\6984Test.t
xt
(null)
output
(null)
C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
\\sqldss01\GNUPG\Transfer\6985Test.gpg -e
\\sqldss01\c$\PGArchive\6985Test.txt
(null)
output
(null)
C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
\\sqldss01\GNUPG\Transfer\6986Test.gpg -e
\\sqldss01\c$\PGArchive\6986Test.txt
(null)
output
(null)
C:\WINDOWS\system32>ftp - s:\\sqldss01\GNUPG\Transfer\pg_ftp_files
Invalid command.
Invalid command.
Not connected.
Not connected.
Not connected.
Not connected.
open ftp.ftp_site_was_here.com
username_was_here
password_was_here
binary
put 6984*.gpg
put 6985*.gpg
put 6986*.gpg
quit
> ftp: connect :Connection refused
(null)
And my ftp script looks like this:
open ftp.ftp_site_was_here.com
username_was_here
password_was_here
binary
put 6984*.gpg
put 6985*.gpg
put 6986*.gpg
quitI forgot. This is SQL Server 2000 SP4 (8.00.2040)
"randy" wrote:

> I have a .bat file that uses ftp -s:scriptname that works from a cmd windo
w
> and works from running the .bat file but won't work from xp_cmdshell.
> The sql agent proxy is set to a system administrator domain account that i
s
> also an admin on the server. The folder where the files are is shared wit
h
> everyone full control. Before FTPing, the files are encrytped using
> xp_cmdshell to call and that works fine. I can't figure it out. Can anyo
ne
> see what I'm missing? Here is the output from the job with the dashed lin
es
> edited out:
> Job 'Test InfoTransfer' : Step 1, 'Encrypt the files' : Began Executing
> 2006-06-22 15:12:05
> output
> (null)
> C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
> \\sqldss01\GNUPG\Transfer\6984Test.gpg -e \\sqldss01\c$\PGArchive\6984Test
.txt
> (null)
> output
> (null)
> C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
> \\sqldss01\GNUPG\Transfer\6985Test.gpg -e
> \\sqldss01\c$\PGArchive\6985Test.txt
> (null)
> output
> (null)
> C:\WINDOWS\system32>c:\GNUPG\gpg -r securedata --always-trust --yes -o
> \\sqldss01\GNUPG\Transfer\6986Test.gpg -e
> \\sqldss01\c$\PGArchive\6986Test.txt
> (null)
> output
> (null)
> C:\WINDOWS\system32>ftp - s:\\sqldss01\GNUPG\Transfer\pg_ftp_files
> Invalid command.
> Invalid command.
> Not connected.
> Not connected.
> Not connected.
> Not connected.
> open ftp.ftp_site_was_here.com
> username_was_here
> password_was_here
> binary
> put 6984*.gpg
> put 6985*.gpg
> put 6986*.gpg
> quit
> (null)
> And my ftp script looks like this:
> open ftp.ftp_site_was_here.com
> username_was_here
> password_was_here
> binary
> put 6984*.gpg
> put 6985*.gpg
> put 6986*.gpg
> quit

FTP connection only works in BIDS

I have an FTP task that will only work when running in BIDS.

When trying to run as a package on the server or calling the package from a job, I get the following error in the log file:

OnError,<servername>,<user>,FTP Task,{B2F5BB68-C6F8-4EE5-ABC0-71C3636E3E4A},{B7B41A88-18DD-4AD7-8CDE-9E0C1B74DA02},6/26/2007 12:09:11 PM,6/26/2007 12:09:11 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

When running in BIDS it is fine.

Any know what is causing this?

Can you access the ftp site when RDP'd onto the server? My guess it is blocked.|||

we tried that

we used the old cmd file that was being called by the 'execute' task and it works fine on the server

can connect to ftp, can login, can transfer file, can rename

|||

When trying the command file, was that from a job -

- created in the same was as the SSIS job

- owned by the same SQL user

- and using the same proxy account if one is used at all?

Those points would effect the security context used to host the process within Windows, which in turn may impact credentials passed to the FTP service, or even just having network access or proxy access.

|||

It is because the security level was wiping the passwords when it was deployed.

We have now set it to use a password and it works ok.

|||

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

|||

Reji George wrote:

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

There really isn't a set "format." You just fill in the blanks for server, user name, password, etc...

Did you take note of the Package ProtectionLevel property message above?

Frustrating Backup/Restore Issue

Im running SQL Server 2005

I set up 4 maintenance plans:

1) Full production DBS backup every day at midnight

2) Transaction Backups every 10 minutes starting at 12:10 AM ending at 11:59 PM

3) Full System DBS backups

4) Maintenance on production database: index rebuild, stats, etc.

All the files are sucessfully there but when I try to restore the backup to a new database to make sure its valid it seems I can restore the full backup but the transaction logs seem to be completely disassociated with the full backup. Says there not from the same backup set

I tried the same thing earlier just taking a full backup and then transaction logs in two separate plans and it worked fine. Sometimes it seems to sync up and sometimes it does not. Any help on this issue would be much appreciated

Thanks

Scott

What you're attempting should work just fine, so we need to figure out exactly where it's going wrong.

Information that will help us figure that out:

The exact error text, cut and pasted if possible. That allows us to track down what code is generating the error.|||

Okay first and foremost here is the error message I can when I try to restore the full backup with the first transaction log after the full backup. (Im having trouble copying and pasting) its says:

The volume on Device 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\radb\radb_backup_200609281200.trn' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)

- I am using separate files and separate database maintenance plans (since they need to be schedule at different times) for the full backup and transactional backups

- I am using separate transaction files for each log and not appending

- I am backing up to disk and not directly to tape

- I am using Management Studio and maintenance plans for these operations

Thanks

Scott

|||

It sounds from your description and the error message as if you might be attempting to restore the full backup and the log backup in one command. If that's the case, this is your problem.

You need to restore the full backup first, WITH NORECOVERY, and then restore transaction log backups one by one in order, specifying WITH NORECOVERY until you've got them all applied. At that point you can restore with recovery, and you're done.

|||

Okay I understand what your saying but ...

I have gotten it to work where I can restore and check the full backup with the transaction logs. Its worked twice like that and hasnt worked twice so its the consistency that bothers me. In the meantime I will try to restore the full backup first and then apply the logs.

sql

Frustrating Backup/Restore Issue

Im running SQL Server 2005

I set up 4 maintenance plans:

1) Full production DBS backup every day at midnight

2) Transaction Backups every 10 minutes starting at 12:10 AM ending at 11:59 PM

3) Full System DBS backups

4) Maintenance on production database: index rebuild, stats, etc.

All the files are sucessfully there but when I try to restore the backup to a new database to make sure its valid it seems I can restore the full backup but the transaction logs seem to be completely disassociated with the full backup. Says there not from the same backup set

I tried the same thing earlier just taking a full backup and then transaction logs in two separate plans and it worked fine. Sometimes it seems to sync up and sometimes it does not. Any help on this issue would be much appreciated

Thanks

Scott

What you're attempting should work just fine, so we need to figure out exactly where it's going wrong.

Information that will help us figure that out:

The exact error text, cut and pasted if possible. That allows us to track down what code is generating the error.|||

Okay first and foremost here is the error message I can when I try to restore the full backup with the first transaction log after the full backup. (Im having trouble copying and pasting) its says:

The volume on Device 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\radb\radb_backup_200609281200.trn' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)

- I am using separate files and separate database maintenance plans (since they need to be schedule at different times) for the full backup and transactional backups

- I am using separate transaction files for each log and not appending

- I am backing up to disk and not directly to tape

- I am using Management Studio and maintenance plans for these operations

Thanks

Scott

|||

It sounds from your description and the error message as if you might be attempting to restore the full backup and the log backup in one command. If that's the case, this is your problem.

You need to restore the full backup first, WITH NORECOVERY, and then restore transaction log backups one by one in order, specifying WITH NORECOVERY until you've got them all applied. At that point you can restore with recovery, and you're done.

|||

Okay I understand what your saying but ...

I have gotten it to work where I can restore and check the full backup with the transaction logs. Its worked twice like that and hasnt worked twice so its the consistency that bothers me. In the meantime I will try to restore the full backup first and then apply the logs.

frequent periods of slow response

We are running SQL 7.0 on a quad-pentium (? MHz)server.
We have 10-12 production databases on the server, a couple
of them, financial/personnel & Prosecutor Dialog
(atty/courts)) are used quite heavily, but still should
not be enough to bring processing to a stop, quite often.
The system runs OK most of the time (50-60%), slow (20-30)
and dog-slow (10-20%). I have ran performance monitor &
cannot see any coorelation between any values there &
response. I'm not sure of our current patch levels (OS-
W2000 or SQL7.0). Tech mgr. says it appears that one or
more programs is starting & then not releasing the memory
back. Main memory fills and then swap space fills. If
this is the case, something clears the space, because the
system does slow down & sometimes stops for 30-60-180
seconds, but then speeds up, slow & quick, slow, stop &
quick, over & over. No pattern during operations hours,
and no specific processing going on, large searches, etc.
How can I identify which program(s) may be hogging memory,
or what other potential problems could there be, e.g.
behind on patch levels, server and/or SQL7.0 optimization
variables not set correctly, whatever. Any ideas on where
to keep looking to resolve significant response problem.If you've got a paging problem, you've likely got a fundamental resource
allocation / configuration problem on that server.
In an attempt to make an easy kill, I'd first use the task manager to look
for processes with excessive memory usage during a period of slow
responsiveness. You could set up an alert to inform you when this occurs
immediately so you can get to the console in time, or perhaps even start a
log.
You should trace the PagingFile object under a SystemMonitor (perfmon if
you're under NT4) log & check it after periods of slow response. This will
confirm whether you have a paging problem. It's not uncommon for SQL Server
to consume the available memory on the server (as it should) but then some
other application require memory unexpectedly. This is sometimes as simple
as a virus checker or other scheduled app..
I'd also track the Process\Working Set counter for the SQL Server process
instance to see if it's SQL Server causing the problem in a log.
Whatever the case, it sounds like you need to establish which app is causing
the memory usage spike & plan memory allocation on that server more
carefully. It might be a case of setting a max memory config for the SQL
Server so other apps can get at memory when they need it without causing
paging on the server.
HTH
Regards,
Greg Linwood
SQL Server MVP
"errol" <mikeu@.co.davis.ut.us> wrote in message
news:8d8601c37efa$eb26acd0$a601280a@.phx.gbl...
> We are running SQL 7.0 on a quad-pentium (? MHz)server.
> We have 10-12 production databases on the server, a couple
> of them, financial/personnel & Prosecutor Dialog
> (atty/courts)) are used quite heavily, but still should
> not be enough to bring processing to a stop, quite often.
> The system runs OK most of the time (50-60%), slow (20-30)
> and dog-slow (10-20%). I have ran performance monitor &
> cannot see any coorelation between any values there &
> response. I'm not sure of our current patch levels (OS-
> W2000 or SQL7.0). Tech mgr. says it appears that one or
> more programs is starting & then not releasing the memory
> back. Main memory fills and then swap space fills. If
> this is the case, something clears the space, because the
> system does slow down & sometimes stops for 30-60-180
> seconds, but then speeds up, slow & quick, slow, stop &
> quick, over & over. No pattern during operations hours,
> and no specific processing going on, large searches, etc.
> How can I identify which program(s) may be hogging memory,
> or what other potential problems could there be, e.g.
> behind on patch levels, server and/or SQL7.0 optimization
> variables not set correctly, whatever. Any ideas on where
> to keep looking to resolve significant response problem.

Freeing up Log Free Space

We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.com
Check "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:

> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>
|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A
|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com
|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Freeing up Log Free Space

We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:

> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (withou
t
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there ar
e
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up spac
e
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file
.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Freeing up Log Free Space

We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
--
Message posted via http://www.sqlmonster.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via SQLMonster.com" wrote:
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (without
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>> We are running SQL 2005 SP1.
>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
>>cbrichards,
>>This article and the articles to which it links can be a help to you in
>>shrinking the log file and in thinking about what you want to do:
>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>One way to free up space in the log, of course, is to do BACKUP LOG
>>(without
>>TRUNCATE) periodically and then save the backups for as long as you need
>>them, even if just a day or so.
>>However, if you are truncating the log, then what is the point of FULL
>>recovery mode? This provides no more transactional integrity or rollback
>>ability than SIMPLE mode. If you are always truncating, then go ahead and
>>set the database to SIMPLE mode, since that is essentially what you have.
>>RLF
>> We are running SQL 2005 SP1.
>>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
>> TLog?
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
--
Message posted via http://www.sqlmonster.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:
>Both of these commands do the same thing to your log. The both free up space
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>> Thanks Russell.
>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||Correct. - RLF
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
>>Both of these commands do the same thing to your log. The both free up
>>space
>>from inactive virtual log files so that it can be reused.
>>Neither will change the physical size of the log file.
>>The difference between these commands is that the second one will save the
>>transactions that it is clearing out, but the first one won't.
>>The only way to reclaim physical space is to physically shrink the log
>>file.
>>Note that physically shrinking the log file is very different from
>>physical
>>shrinking a datafile. Most of the warnings in Tibor's article have to do
>>with shrinking data files.
>> Thanks Russell.
>>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

fragmented database files

I have a sql server 2005 server, running on windows server 2003 sp2 on
a 32 bit dual processor dell machine.
I have have some smallish databases on this machine. The drives which
hold the MDF and LDF files have become badly fragmented. Some of the
log files have been split into tens of fragments. The distribution
database has over 100 fragments. I can see that this is bad for
performance, how bad? In the past, this machine has become very
unhappy when producing replication snapshots (with lots of page io
latch* waits). Now it seems to have become CPU bound (showing SOS
scheduler waits). I'm not saying these things are connected, but just
throwing out some data.
Any thoughts on defragmenting, reducing fragmentation in the future
and any connection to (recently) worsening performance?
One idea I had is that there is plenty of space on the drives (>50%)
if I shut down sql server and then moved the files to another physical
drive and then copied them back, would that put them back in a non-
fragmented way (given that there is a contiguos block of free space
that is big enough.. it looks like there is from defrag console, but
I'm not sure how accurate that is)
thanks for any helpHi Sam
Disc fragmentation is usually less of an issue if you have a dedicated file
server and don't (auto) shrink the databases. On a database server (such as
a development PC) which has shared usage it may be more of an issue
especially if you are continually backing up/restoring databases (or if you
shrink the database). See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more.
To defragment the database files you will either have to detach the database
or stop SQL server while you run a disc defragmenter. Tools such as
diskeeper can help reduce fragmentation.
John
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>|||If your database files are as extremely fragmented as you describe
then I suggest a few things.
1) This sort of problem can result from using autoshrink. Check to
see if it is being used, and if it is remove it! At the same time
size the files so that autogrow is not needed.
2) Defragment the drives. The database file fragmentation might can't
be helping your performance problems, and might improve things.
3) Monitor the system. Expand the files before they autogrow - the
worst time to expand a file is when a transaction has to wait for the
growth to complete. Add space in large chunks, not small bits.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Dec 2007 06:15:47 -0800 (PST), "sam.m.gardiner"
<sam.m.gardiner@.gmail.com> wrote:
>I have a sql server 2005 server, running on windows server 2003 sp2 on
>a 32 bit dual processor dell machine.
>I have have some smallish databases on this machine. The drives which
>hold the MDF and LDF files have become badly fragmented. Some of the
>log files have been split into tens of fragments. The distribution
>database has over 100 fragments. I can see that this is bad for
>performance, how bad? In the past, this machine has become very
>unhappy when producing replication snapshots (with lots of page io
>latch* waits). Now it seems to have become CPU bound (showing SOS
>scheduler waits). I'm not saying these things are connected, but just
>throwing out some data.
>Any thoughts on defragmenting, reducing fragmentation in the future
>and any connection to (recently) worsening performance?
>One idea I had is that there is plenty of space on the drives (>50%)
>if I shut down sql server and then moved the files to another physical
>drive and then copied them back, would that put them back in a non-
>fragmented way (given that there is a contiguos block of free space
>that is big enough.. it looks like there is from defrag console, but
>I'm not sure how accurate that is)
>thanks for any help
>|||1) You should set the database and log file sizes such that you never
actually HIT an autogrow. That mechanism should only be for exceptional,
unexpected situations. Making the files big enough to handle say 1.5 years
of data growth leads to a contiguous disk file (and thus sequential I/O) and
no fragmentation.
2) I had a client where I got back 18% throughput simeply by having them
defrag their disks. They had well over 300K database and log file fragments
though!! :) That is what can happen when you use default settings.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>

fragmented database files

I have a sql server 2005 server, running on windows server 2003 sp2 on
a 32 bit dual processor dell machine.
I have have some smallish databases on this machine. The drives which
hold the MDF and LDF files have become badly fragmented. Some of the
log files have been split into tens of fragments. The distribution
database has over 100 fragments. I can see that this is bad for
performance, how bad? In the past, this machine has become very
unhappy when producing replication snapshots (with lots of page io
latch* waits). Now it seems to have become CPU bound (showing SOS
scheduler waits). I'm not saying these things are connected, but just
throwing out some data.
Any thoughts on defragmenting, reducing fragmentation in the future
and any connection to (recently) worsening performance?
One idea I had is that there is plenty of space on the drives (>50%)
if I shut down sql server and then moved the files to another physical
drive and then copied them back, would that put them back in a non-
fragmented way (given that there is a contiguos block of free space
that is big enough.. it looks like there is from defrag console, but
I'm not sure how accurate that is)
thanks for any help
Hi Sam
Disc fragmentation is usually less of an issue if you have a dedicated file
server and don't (auto) shrink the databases. On a database server (such as
a development PC) which has shared usage it may be more of an issue
especially if you are continually backing up/restoring databases (or if you
shrink the database). See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more.
To defragment the database files you will either have to detach the database
or stop SQL server while you run a disc defragmenter. Tools such as
diskeeper can help reduce fragmentation.
John
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>
|||If your database files are as extremely fragmented as you describe
then I suggest a few things.
1) This sort of problem can result from using autoshrink. Check to
see if it is being used, and if it is remove it! At the same time
size the files so that autogrow is not needed.
2) Defragment the drives. The database file fragmentation might can't
be helping your performance problems, and might improve things.
3) Monitor the system. Expand the files before they autogrow - the
worst time to expand a file is when a transaction has to wait for the
growth to complete. Add space in large chunks, not small bits.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Dec 2007 06:15:47 -0800 (PST), "sam.m.gardiner"
<sam.m.gardiner@.gmail.com> wrote:

>I have a sql server 2005 server, running on windows server 2003 sp2 on
>a 32 bit dual processor dell machine.
>I have have some smallish databases on this machine. The drives which
>hold the MDF and LDF files have become badly fragmented. Some of the
>log files have been split into tens of fragments. The distribution
>database has over 100 fragments. I can see that this is bad for
>performance, how bad? In the past, this machine has become very
>unhappy when producing replication snapshots (with lots of page io
>latch* waits). Now it seems to have become CPU bound (showing SOS
>scheduler waits). I'm not saying these things are connected, but just
>throwing out some data.
>Any thoughts on defragmenting, reducing fragmentation in the future
>and any connection to (recently) worsening performance?
>One idea I had is that there is plenty of space on the drives (>50%)
>if I shut down sql server and then moved the files to another physical
>drive and then copied them back, would that put them back in a non-
>fragmented way (given that there is a contiguos block of free space
>that is big enough.. it looks like there is from defrag console, but
>I'm not sure how accurate that is)
>thanks for any help
>
|||1) You should set the database and log file sizes such that you never
actually HIT an autogrow. That mechanism should only be for exceptional,
unexpected situations. Making the files big enough to handle say 1.5 years
of data growth leads to a contiguous disk file (and thus sequential I/O) and
no fragmentation.
2) I had a client where I got back 18% throughput simeply by having them
defrag their disks. They had well over 300K database and log file fragments
though!! That is what can happen when you use default settings.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>

fragmented database files

I have a sql server 2005 server, running on windows server 2003 sp2 on
a 32 bit dual processor dell machine.
I have have some smallish databases on this machine. The drives which
hold the MDF and LDF files have become badly fragmented. Some of the
log files have been split into tens of fragments. The distribution
database has over 100 fragments. I can see that this is bad for
performance, how bad? In the past, this machine has become very
unhappy when producing replication snapshots (with lots of page io
latch* waits). Now it seems to have become CPU bound (showing SOS
scheduler waits). I'm not saying these things are connected, but just
throwing out some data.
Any thoughts on defragmenting, reducing fragmentation in the future
and any connection to (recently) worsening performance?
One idea I had is that there is plenty of space on the drives (>50%)
if I shut down sql server and then moved the files to another physical
drive and then copied them back, would that put them back in a non-
fragmented way (given that there is a contiguos block of free space
that is big enough.. it looks like there is from defrag console, but
I'm not sure how accurate that is)
thanks for any helpHi Sam
Disc fragmentation is usually less of an issue if you have a dedicated file
server and don't (auto) shrink the databases. On a database server (such as
a development PC) which has shared usage it may be more of an issue
especially if you are continually backing up/restoring databases (or if you
shrink the database). See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more.
To defragment the database files you will either have to detach the database
or stop SQL server while you run a disc defragmenter. Tools such as
diskeeper can help reduce fragmentation.
John
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>|||If your database files are as extremely fragmented as you describe
then I suggest a few things.
1) This sort of problem can result from using autoshrink. Check to
see if it is being used, and if it is remove it! At the same time
size the files so that autogrow is not needed.
2) Defragment the drives. The database file fragmentation might can't
be helping your performance problems, and might improve things.
3) Monitor the system. Expand the files before they autogrow - the
worst time to expand a file is when a transaction has to wait for the
growth to complete. Add space in large chunks, not small bits.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Dec 2007 06:15:47 -0800 (PST), "sam.m.gardiner"
<sam.m.gardiner@.gmail.com> wrote:

>I have a sql server 2005 server, running on windows server 2003 sp2 on
>a 32 bit dual processor dell machine.
>I have have some smallish databases on this machine. The drives which
>hold the MDF and LDF files have become badly fragmented. Some of the
>log files have been split into tens of fragments. The distribution
>database has over 100 fragments. I can see that this is bad for
>performance, how bad? In the past, this machine has become very
>unhappy when producing replication snapshots (with lots of page io
>latch* waits). Now it seems to have become CPU bound (showing SOS
>scheduler waits). I'm not saying these things are connected, but just
>throwing out some data.
>Any thoughts on defragmenting, reducing fragmentation in the future
>and any connection to (recently) worsening performance?
>One idea I had is that there is plenty of space on the drives (>50%)
>if I shut down sql server and then moved the files to another physical
>drive and then copied them back, would that put them back in a non-
>fragmented way (given that there is a contiguos block of free space
>that is big enough.. it looks like there is from defrag console, but
>I'm not sure how accurate that is)
>thanks for any help
>|||1) You should set the database and log file sizes such that you never
actually HIT an autogrow. That mechanism should only be for exceptional,
unexpected situations. Making the files big enough to handle say 1.5 years
of data growth leads to a contiguous disk file (and thus sequential I/O) and
no fragmentation.
2) I had a client where I got back 18% throughput simeply by having them
defrag their disks. They had well over 300K database and log file fragments
though!! That is what can happen when you use default settings.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
=============
VB .NET Developer
http://www.rocketscience.uk.com
Exactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
=============
VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in mes
sage
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So whi
ch
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
============= VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com