Skip to main content

Posts

Showing posts from November, 2011

Backup Statistics and History - SQL Server

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at the other place and using for the database restore which used for reporting purposes or used in log shipping purpose. Because database backups are the most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loss. You can read my earlier posts Database Backup Compression ,  Database Backup files Verification ,  Automated All Databases Backups Script   and  Split Database Full Backup to Multiple files . I would like to share the script which helps us to show the database backup status, history of theirs when they are done based on schedule, at where are taking and when, backup types, backups, physical device and the size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statist

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server

When we created a new database, it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from a table in another session while running update transaction? How can ? NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other options other than this. But I am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here. For NOLOCK, we need to put this hint on table level, so it is required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written on top of the query or stored procedure. Let us look on small demo to elaborate it. First checking he

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of the instances of registered SQL Server instances. There you can see I have registered one, shared SQL Server 2011 instance, under CMS and named it Denali. Under Shared instance, I have registered SQL Server 2008 and same SQL Server instance of Denali which I already registered. Here SQL server 2008 successfully registered but SQL Server 2011 has encountered an error. But how I have registered it which I am going to explain here. This is because the same SQL instance already registered as shared SQL server. How can i register same SQL Server Denali or 2011 instance again? I have changed the port and applied static port as follows from SQL Server TCP/IP properties which we will available on the SQL Server Configuration Monitor. Then please see the screenshot below as I have registered SQL Server Denali instance with port.

Database Backup Compression, Amazing feature for DBA - SQL Server 2008

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more time to execute and very CPU, memory and IO consumptive. And the important thing is backups activity should be completed within down time or pick a time when more users are not connected with databases. What is the solution? We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provides the best feature and supported SQL Server 2008 or newer version. That is "Backup Compression". You can read my earlier posts for   Automated All Databases Backups , Database Backup files Verification and Details and Split Database Full Backup to Multiple files Using this feature, we can take a database backup with compression option. And will really reduce the time required to backup it, reduce server IO and less CPU and memory consum

Review of some replication issues and workaround - SQL Server

After posting some snaps to configure a transactional replication in SQL server having publication server as a distributor , I want to share some of the issues which I got during working with it. You might also face the same issue and got resolved as well. You can comment if you faced any other replication issues and the solution for the same. 1."There is insufficient system memory to run this query" - Distributor agent error for subscriber database : During working with replication this is one of the errors which I encountered and get resolved it. This is coming with a Distributor agent, and it stopped working. It's due to the memory issue . I have flashed the unused memory consumed by Adhoc queries and system cache. The error, " There is insufficient system memory to run this query. (Source: MSSQL_REPL, Error number: MSSQL_REPL27453) " To flush the memory uses the below commands for a solution of above error, DBCC FREESESSIONCACHE DB

Multi Monitor Support of SSMS - A new feature of SQL Server Denali

I have drafted all the new features and enhancements introduced by SQL Server Denali CTP1 and CTP3 .   Multi Monitor Support is the new feature among them. What can w e do ? 1. Using this feature we can use multi screen of query analyzer or editor. 2. You can monitor separately it and run the output individually. 3. You can resize all the screens. 4. You can drag and drop at the place you want. 5. You can use Registered servers and object explorer and it's details with multi screens. For more idea, You can see the below screen shows which I have captured during enjoyed with it. 1. This screen shot has multi screens of query analyzer with SSMS. 2. This screen shot captured during docking/undocking the screens. Hope you like this post.