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

To Shrink or Compress the DB file or DB log

MS SQL Server 2005

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

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


MS SQL Server 2008

USE [DatabaseName]
GO
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(<TransactionLogName>, 1)
ALTER DATABASE [DatabaseName] SET RECOVERY FULL WITH NO_WAIT
GO

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

Password for a User

Password for CITI (password) is

ABC9E53630D151F9599562DACB79C1A15F54EBBFD812A581C0340F0E632794B6

Note:- CITI is using SHA256 algorithm. For other banks md5 algorithm is used

Rename Database Name in MS SSL Server

Use the below script to Rename Database
ALTER DATABASE [old_name]
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [old_name]
 MODIFY NAME = [new_name]
GO
ALTER DATABASE [new_name]
 SET MULTI_USER
GO

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)

Total are not Equal Issue

In the Salary / Refund Review screen, if the error "Total are not equal" is displayed reason being the number of records and the calculated charges are null

Resolution for the same:

 Update the SP (WPS_sp_Select_FIS_Details) for the FIS_DET value with the value
 isnull(FISDET_Deleted, '0') not in ('1')

TAB Pages alignment Issue

TAB pages module alignment (ex employer master detail and Charges module are getting mixed in one page)

 Resolution for the same :

  Include the two folders
            1. aspnet_client and
            2. webctrl_client in the C:\Inetpub\wwwroot path to resolve the same

To get DLL backup from the GAC

To get the DLL file from the GAC

 1. Go to C:\windows\assembly\gac_msil
 2. Copy the required DLL file

Introduction

This blog is for our Team to share and maintain the technical stuff