Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select log_reuse_wait_desc from sys.databases where name = 'tempdb'
- #0AF29B96
- #0B5CAFEA
- #0BE6BFCF
- #0C50D423
- SELECT SUM(unallocated_extent_page_count) AS [free pages],
- (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],
- 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],
- SUM(user_object_reserved_page_count) AS [user object pages used],
- (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
- FROM sys.dm_db_file_space_usage;
- SELECT SUM(size)*1.0/128 AS [size in MB]
- FROM tempdb.sys.database_files
- free pages free space in MB internal object pages used internal object space in MB user object pages used user object space in MB
- ------------ ----------------- -------------------------- ---------------------------- ---------------------- -----------------------
- 1829424 14292.375000 320 2.500000 80 0.625000
- size in MB
- -------------
- 22490.062500
- SELECT t1.name AS [Service_Name], t3.name AS [Schema_Name], t2.name AS [Queue_Name],
- CASE WHEN t4.state IS NULL THEN 'Not available'
- ELSE t4.state
- END AS [Queue_State],
- CASE WHEN t4.tasks_waiting IS NULL THEN '--'
- ELSE CONVERT(VARCHAR, t4.tasks_waiting)
- END AS tasks_waiting,
- CASE WHEN t4.last_activated_time IS NULL THEN '--'
- ELSE CONVERT(varchar, t4.last_activated_time)
- END AS last_activated_time ,
- CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--'
- ELSE CONVERT(varchar,t4.last_empty_rowset_time)
- END AS last_empty_rowset_time,
- (
- SELECT COUNT(*)
- FROM sys.transmission_queue t6
- WHERE (t6.from_service_name = t1.name)
- ) AS [Tran_Message_Count]
- FROM sys.services t1
- INNER JOIN sys.service_queues t2
- ON ( t1.service_queue_id = t2.object_id )
- INNER JOIN sys.schemas t3
- ON ( t2.schema_id = t3.schema_id )
- LEFT OUTER JOIN sys.dm_broker_queue_monitors t4
- ON ( t2.object_id = t4.queue_id AND t4.database_id = DB_ID() )
- INNER JOIN sys.databases t5
- ON ( t5.database_id = DB_ID() )
- Service_Name Schema_Name Queue_Name Queue_State tasks_waiting last_activated_time last_empty_rowset_time Tran_Message_Count
- ------------------------------------------------------------------------- ------------ ----------------------------- --------------- -------------- -------------------- ----------------------- -------------------
- InternalMailService dbo InternalMailQueue INACTIVE 0 Apr 7 2013 8:10AM Apr 7 2013 8:10AM 0
- ExternalMailService dbo ExternalMailQueue INACTIVE 0 Apr 7 2013 8:10AM Apr 7 2013 8:10AM 0
- http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService dbo QueryNotificationErrorsQueue Not available -- -- -- 0
- http://schemas.microsoft.com/SQL/Notifications/EventNotificationService dbo EventNotificationErrorsQueue Not available -- -- -- 0
- http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker dbo ServiceBrokerQueue Not available -- -- -- 0
- SELECT OBJECT_NAME(id), rowcnt
- FROM tempdb..sysindexes
- WHERE OBJECT_NAME(id) LIKE '#%'
- ORDER BY rowcnt DESC
- SELECT
- SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
- SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
- SUM (version_store_reserved_page_count)*8 as version_store_kb,
- SUM (unallocated_extent_page_count)*8 as freespace_kb,
- SUM (mixed_extent_page_count)*8 as mixedextent_kb,
- SUM (User_object_reserved_page_count +
- internal_object_reserved_page_count +
- version_store_reserved_page_count +
- unallocated_extent_page_count +
- mixed_extent_page_count) * 8 as total_space
- FROM sys.dm_db_file_space_usage
- USE [tempdb]
- GO
- DBCC FREEPROCCACHE -- this will free up all cache plans
- GO
- USE [tempdb]
- GO
- DBCC SHRINKFILE (N'tempdev' , 10000) -- will shrink the mdf file to 10GB, you can specify log file as well
- GO
Add Comment
Please, Sign In to add comment