November 9, 2016 at 2:44 am
I would like to know the t-log grow as the databases are in simple recovery , how can we calculate the growth of transaction log file ?
November 9, 2016 at 5:37 am
ramyours2003 (11/9/2016)
I would like to know the t-log grow as the databases are in simple recovery , how can we calculate the growth of transaction log file ?
You can't really do that, it doesn't grow unless VLFs cannot be reused
😎
November 9, 2016 at 5:56 am
Your transaction log size is a product of the activity in your database. You're in simple recovery mode, so as long as there's nothing preventing the log from being truncated (such as replication) the transaction log usually only needs to be large enough to accommodate the largest transaction. In many cases, that will be rebuilding your largest index. In others, it will be the data load for your largest table (assuming the transaction isn't minimally logged). That is all something of a simplification: I recommend you spend an afternoon reading about transaction log management.
John
November 9, 2016 at 6:54 am
Thaks for the reply , how can we find the largest index in a database ?
November 9, 2016 at 7:02 am
As I said in the thread where you asked that (http://www.sqlservercentral.com/Forums/Topic1833167-3739-1.aspx), don't use the largest index to judge log size. Assuming this is an active system, measure the % used of the log at regular intervals and take the max size reached as a target for resizing the log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2016 at 7:08 am
I'm sure if you type "find largest index in database" into your favourite search engine, you'll get a wealth of results. Or you could right-click on your database, choose Reports -> Standard Reports ->Disk Usage by Table. This won't tell you the size of individual indexes, but it will tell you which tables have the largest total index space.
John
November 9, 2016 at 4:19 pm
I would tend to use Gail's advice. However, if index rebuilds are your largest operation, and you can size the log for that, then it's not a bad way to set a peak. I'd pad this and allow for growth. After all, your indexes will grow.
Likely your largest table with a clustered index is your largest index.
November 10, 2016 at 8:11 am
ramyours2003 (11/9/2016)
I would like to know the t-log grow as the databases are in simple recovery , how can we calculate the growth of transaction log file ?
as already discussed, don't use index size alone. But if you really need a script to show index sizes for all objects (and yes even a heap table has an entry in sys.indexes) see your other post
Steve Jones - SSC Editor (11/9/2016)
I would tend to use Gail's advice. However, if index rebuilds are your largest operation, and you can size the log for that, then it's not a bad way to set a peak. I'd pad this and allow for growth. After all, your indexes will grow.Likely your largest table with a clustered index is your largest index.
or if it's a heap it'll likely be that which is the largest object
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2016 at 11:37 pm
This is a script that I use when I need to know how much space each table is using
SET NOCOUNT ON;
CREATE TABLE #temp (
table_name sysname
, row_count INT
, reserved_size VARCHAR(50)
, data_size VARCHAR(50)
, index_size VARCHAR(50)
, unused_size VARCHAR(50)
);
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?''';
SELECT
a.table_name
, col_count = COUNT(*)
, a.row_count
, a.data_size
, a.index_size
, a.unused_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default = b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size, a.index_size, a.unused_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC;
DROP TABLE #temp;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply