Friday, December 30, 2011

To Shrink or Compress the DB file or DB log




USE DatabaseName

GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO



Note:- Provide the Database name and the TransactionLogName within single quotes

To Know the DB file size consumption

Below query should output the DB file consumption details



SELECT

DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC

Tuesday, December 6, 2011

Issue with the Message Queue

If there is a message queue problem with the steps then it can be resolved upon re-installing the message queue.
Please follow the below steps to re-install the message queue.
1. Go to Control Panel -> Add or Remove Programs -> Add/Remove Windows Components -> Uncheck Message Queuing -> continue Next steps. This should uninstall the Message Queuing Service from the system
2. Restart the Machine (Server)
3. Control Panel -> Add or Remove Programs -> Add/Remove Windows Components -> Check Message Queuing -> Click the Details tab -> Ensure selecting MSMQ HTTP Support, Common and Triggers -> Click OK button -> Click Next. This should install the Message Queuing Service in the system
4. Restart the Machine (Server)
There was an error in this gadget