log file sizes for all databases

Hi All,

I ran the following query to get the log file sizes for all databases:

select (size*8.00)/1024,filename from master..sysaltfiles

When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.I have checked that and find the difference.

Run the below code which gives you the size of tempdb which is as same as physically stored file on your harddisk.

sp_helpdb 'tempdb'

or

select name, filename, (size*8) as size from tempdb..sysfiles

The size in the master..sysaltfiles will helps you to determine the size of files in the tempdb when SQL Server was last started.

Refer the below web page & Sr # 6. RESOURCES

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=39550|||I've ran the statement that you sent me and I am still coming up with wrong number.|||I've ran the statement that you sent me and I am still coming up with wrong number.

The statement I have given will help you to figure out current tempdb log size.

The size in the master..sysaltfiles will help you to determine the size of the files in the tempdb when SQL Server was last started.

Refer the link I have posted above.

Log backups to the same file

SQL Server 2000
If you write log backups to the same filename, does the data append, or does
it overwrite?Hi Jay
The INIT/NOINIT options for the backup command can be used for BACKUP LOG as
well as BACKUP DATABASE. In general the INIT will overwrite although there
are a few caveats
From BOL:
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.
The backup media is not overwritten if any one of the following conditions
is met:
All backup sets on the media have not yet expired. For more information.
The backup set name given in the BACKUP statement, if provided, does not
match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information about
interactions when using SKIP, NOSKIP, INIT, and NOINIT.
Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it.
NOINIT
Indicates that the backup set is appended to the specified disk or tape
device, preserving existing backup sets. NOINIT is the default.
John
"Jay" wrote:
> SQL Server 2000
> If you write log backups to the same filename, does the data append, or does
> it overwrite?
>
>|||That depends on your usage of the WITH INIT/NOINIT in your BACKUP statement
"Jay" <spam@.nospam.org> wrote in message
news:eSo2bsf6HHA.3400@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000
> If you write log backups to the same filename, does the data append, or
> does it overwrite?
>|||I have always put a date/time stamp on the TRN filenames, but the system I'm
currently looking at is just appending to the same TRN file all day, with
another system doing the write to tape.
I never even thought about doing it like that, but it does seem cleaner, in
that the pickup routine can specify exact filenames. The only real problem I
see is when the log backup is running and the pickup tries to take the file.
Which is best practices?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECB75004-5B14-4F10-9D3F-A4C9D64617FA@.microsoft.com...
> Hi Jay
> The INIT/NOINIT options for the backup command can be used for BACKUP LOG
> as
> well as BACKUP DATABASE. In general the INIT will overwrite although there
> are a few caveats
> From BOL:
> INIT
> Specifies that all backup sets should be overwritten, but preserves the
> media header. If INIT is specified, any existing backup set data on that
> device is overwritten.
> The backup media is not overwritten if any one of the following conditions
> is met:
> All backup sets on the media have not yet expired. For more information.
> The backup set name given in the BACKUP statement, if provided, does not
> match the name on the backup media. For more information, see the NAME
> clause.
> Use the SKIP option to override these checks. For more information about
> interactions when using SKIP, NOSKIP, INIT, and NOINIT.
> Note If the backup media is password protected, SQL Server does not write
> to the media unless the media password is supplied. This check is not
> overridden by the SKIP option. Password-protected media may be overwritten
> only by reformatting it.
> NOINIT
> Indicates that the backup set is appended to the specified disk or tape
> device, preserving existing backup sets. NOINIT is the default.
> John
> "Jay" wrote:
>> SQL Server 2000
>> If you write log backups to the same filename, does the data append, or
>> does
>> it overwrite?
>>