So here we are talking about SQL Server and SQL databases, there are many different types of method and strategy for administrators to so there efficiency in better way. Many Database Administrators are not knowing the prefect tips to increase the production or how to verify production data.. In this part we are discussing about the tips that imperative to know by any Database Administrators.
1) Use forfiles to get rigid of old data
Forfiles (forfile.exe) usefulness are already pre- installed in Window server 2003. It empowers the administrators to achieve batch file processing. By utilising this service and SQL server Agent, to which Database Administrators can delete the old heavy data backups, which will eventually eliminate the dependency on SQL server production.
2) Using of ‘ALTER USER’ to restore the unknown logins
The ‘ALTER USER’ command of T- SQL’s have a WITH LOGIN condition too, from SQL server 2005 SP2. By choosing this as option will repair the unknown logins by revising the user’s Sid for security identifier of server login. It will not enhance only SQL server but also the window server. When a database is used by any unknown server it is recognised as Orphaned user or unknown user, and that particular login is totally independent.
3) Use the ‘sp_addsrvrolemember’ to take up sysadmin role
Sysadmin role is not present in Window administrators by any automated way, from SQL server 2008. To recover this and get benefit of sysadmin role you can start using SQL server as single- user mode or maintenance mode after that you ready to run ‘sp_addsrvrolemember’ in Sqlcmd utility to register your login as sysadmin role.
4) Using PortQryUI for troubleshooting for connecting issues
By using the Microsoft PortQryUI to vanish the problems related to TCP/ IP connectivity issue said RemoteDBA.com. Perviously PortQry was there to solve this issue but after the alternative to PortQryUI present but it also has GUI as pre- defined services. One of the pre defined services are for SQL server, composed of TCP port 1433 UDP port 1434. If you want to check the port you can simply enter the IP address or the FQDN (Fully Qualified Domain Name) or target SQL. It’s easy to get PortQryUI utility at ‘PortQryUI’.
5) Use a different planning while running the DBCC CHECKDB against large database
Databases are becoming larger by every passing day, for the maintenance such as checking integrity with DBCC CHECKDB command of T- SQL’s may take long time. One fastest solution is to access backup to get back database on different server and after that run the DBCC CHECKDB against the existing server.