April 10, 2014 at 9:37 am
Hi smart folks,
I am looking for some scripts or usp_, that give me report of tempdb uses every hours or days plus same way of user database growth report everyday or weeks or months.
Thank you
April 10, 2014 at 10:52 am
There are countless scripts out there for this, what specifically are you wanting to track?
Try this link 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 10, 2014 at 10:58 am
Thanks for your input, but doesn't help much. looking for custom tempdb growth report and databases growth report.
April 10, 2014 at 11:08 am
Okay...
To collect database growth over time, you could use something like this: SELECT database_name ,
BackupDate = CONVERT(VARCHAR(10), backup_start_date, 111) ,
SizeInGigs = ( backup_size / 1024000000 )
FROM msdb..backupset
WHERE type = 'd'
ORDER BY database_name ,
backup_start_date DESC
To get a great explanation of how to set it all up, please refer to this article here on SSC - http://www.sqlservercentral.com/articles/Monitoring/66257/
To determine the amount of "free" space in tempdb:SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
To determine free space used by the version store:SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
To determine long running transactions:SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
To determine the amount space used by internal objects: SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
To determine the amount space used by user objects: SELECT SUM(user_object_reserved_page_count) AS ,
(SUM(user_object_reserved_page_count)*1.0/128) AS
FROM sys.dm_db_file_space_usage;
Etc...
This page will outline all the scripts needed t monitor tempDB: http://msdn.microsoft.com/en-us/library/ms176029.aspx
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2014 at 11:56 am
Thank you so much for your valuable time.
April 11, 2014 at 12:07 pm
One more thing, when I run sys.databases, on my laptop Adventure works it shows 'log_resue_wait_desc' = ACTIVE_TRANSACTION
I ran pretty much every thing like 'dbcc opentran', sys.dm_tran_session_transactions, sys.dm_tran_database_transactions, But still not able to locate what transaction is active!!!!
Would you be able to tell me where and how to find and how to make 'log_resue_wait_desc' = NOTHING?
April 11, 2014 at 12:27 pm
one thing, when I ran your 1st script about backup history and I filtered by database_name, I am getting two same backup dates, although I have a backup job to run once everyday.
I am getting results like this:
Database_name Backupdate sizeinGigs
Adworks 4/10/2014 1.11gb
Adworks 4/10/2014 1.10gb
Adworks 4/09/2014 0.96gb
Adworks 4/09/2014 0.95gb
Adworks 4/08/2014 0.92gb
Adworks 4/08/2014 0.91gb
April 11, 2014 at 2:19 pm
smtzac (4/11/2014)
one thing, when I ran your 1st script about backup history and I filtered by database_name, I am getting two same backup dates, although I have a backup job to run once everyday.I am getting results like this:
Database_name Backupdate sizeinGigs
Adworks 4/10/2014 1.11gb
Adworks 4/10/2014 1.10gb
Adworks 4/09/2014 0.96gb
Adworks 4/09/2014 0.95gb
Adworks 4/08/2014 0.92gb
Adworks 4/08/2014 0.91gb
If you have two backups for the 9th it's because 2 backups ran. Check your job and view the history
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 15, 2014 at 2:31 pm
NO. backup once a day!!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply