As a DBA it may be a challenging task to gather some system level or at business level some specific or few data needs to be check at SQL Server startup level. SQL Server provides this feature where we can execute stored procedure when SQL server instance starts and as per requirement we can analyze the data. For same first check system configuration, the option ‘scan for startup procs’ should be set 1.
Steps are as below,

– Step 1 – Lets check Where ‘show advanced options’ option is configured or not, if not, first configure it as

sp_configure ‘show advanced options’ ,1
Reconfigure with override
Go 

– Step 2 – Configure ‘scan for startup procs’ option as

sp_configure ‘scan for startup procs’ ,1

Reconfigure with override
Go

From above syntax, server is now enabled to execute stored procedure at instance startup time.

As we know, when SQL Server starts, it first scans the registry to check the startup parameter values, during this it’s find the master database files and make the master database online. Now, Master Database is online, and startup procedure should be in Master Database and that SP will be execute from Master DB only. (Here is a restriction – Our SP should be in Master DB).

The sp_procoption stored procedure is used to execute the SP when SQL Server service starts as,

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘true’

We can turn-off the Stored procedure execution at SQL server instance startup using below query.

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘false’

Please comment on this topic.

Today morning, my one friend call me, saying there is a bug with SQL Server. As per his statement after renaming a Stored procedure name from SSMS, SP_HELPTEXT showing old name in script while SSMS is showing changed name, I think it’s not a system bug because its happens when the store procedure is renamed using right click and rename in GUI or using SP_rename <object name>, the sys.syscomments system table is not getting updated and SQL statement shown by sp_helptext is from sys.syscomments. by using ALTER statement sys.syscomments get updated. So, Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, it’s recommend that sp_rename/or from GUI, should not be used to rename these object types. We can use alter statement or drop and re-create the object with its new name.

It’s a very common and sometime frustrating/irritating task for a DBA to trouble shoot SQL Server performance issues. With SQL server 2008 a very useful concept i.e. Management Data Warehouse – MDW introduced for continuous performance monitoring. MDW has an API that allows recording of performance counters from components outside of SQL Server parallel to the recording of built-in SQL Server and Operating System performance counters. MDW captures every standard performance counter traditionally used for performance analysis. MDW captures key Windows Performance Monitor (PerfMon) counters and internal SQL Server counters automatically into a SQL Server database. The collected data is being stored in SQL server Database. The following is the illustration of MDW.

Server installation installs MDW automatically. Let’s activating MDW step-by-step in SQL server 2008.

  1. In SQL Server Management Studio (SSMS), open Management, Data Collection and then right-click and select Configure Management Data Warehouse.
  2. It will show a welcome screen, Select [Next], Now “Select configuration task” dialog will be appear, Select “Create or upgrade a management data warehouse” and then click [Next].

     

  3. On the Configure Management Data Warehouse Storage screen, click [New].

     

  4. When the [New Database] dialog box appears, enter DB Name and set your prefer location of this DB. Click [OK].

     

  5. The [Configure Management Data Warehouse Storage] dialog will re-appear, click [Next].

     

  6. The [Map Login and Users] dialog will appear. If you are an administrator, there is no need to add a mapping, otherwise add assign a login/user. Click [Next].

     

  7. On the [Complete the Wizard] dialog, click [Finish].

     

  8. Next, the [Configure Data Collection Wizard Progress] dialog. When it completes, click [Close].

     

  9. Now a new database (as per given name) has been created. In SSMS it will be shown.

     

  10. Now we have created a MDW database, which will be used to store other server’s data for monitoring purpose, Let we have 3 server named as Server A, Server B and Server C as per above MDW illustration, we want to capture data from all these 3 servers, for same we have to follow these steps on each server from point 10 to 12.

    Now connect Server A, in SSMS, go to Management, Data Collection and then right-click and “select Configure Management Data Warehouse menu” item. On welcome screen, select [Next], This time, however, “select Set up data collection” and click [Next].

     

  11. Now on “Configure MDW Storage”, Select your server name and DB name that is the host for MDW.

     

  12. Click on [Next], then click [Finish], then Close.

    The same thing should be repeated on all servers which needs to be monitor.

     

    Note: Its observation, if all servers are in a domain, there should be an AD account to start SQL agent services on each server which are being monitored.

     

    Go to your MDW database , right click on it, Select “Reports”, then “Management Data Warehouse” then “Management Data Warehouse overview”, all servers will be displayed and now we will able to monitor server’s “Server Activity”, “Query Statistics” and “Disk Usage” will drill down reporting facilities.

     

     

It sometimes happened (hope all of us experienced this one) that during working with SQL Server Management Studio (SSMS) it got crashed for whatever reason. For same there is a chance that we will lose whatever queries we had open at the time of the crash. Sometimes when we re-open SSMS it will ask us to re-open any files that we recover but for some reason it doesn’t always do that and in that instance we think that our work has gone for good.

Here is a way to get some information, even if SSMS does not prompt us to recover those files they are probably still present on your machine. Go and check in your C:\Users\Virendra\Documents\SQL Server Management Studio\Backup Files\Solution1 folder.


Please share your good/bad experiences …

 

It’s a very common practice every DBA verifies where restored Database is as per expected or not, and there may be a question where restored database is restored from proper backup or not. Here is a query which will show how we can identify the file(s) that was used for the restore, when the backup actually occurred and when the database was restored.

SELECT RH.[destination_database_name] Destination_DB_Name, RH.[restore_date] Restore_Date, BKS.[backup_start_date] Backup_StartDateTimeBKS.[backup_finish_date] Backup_FinishDateTime,
BKS.[database_name] 
as [Source_Database_Name], BMF.physical_device_name] as [Backup_file_used_for_Restore],
RF.file_number File_number,RF.destination_phys_drive Restore_TragetDrive,
RF.destination_phys_name 
Restore_TragetFileName
FROM msdb..restorehistory RH
INNER JOIN msdb..restorefile RF on RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb..backupset BKS ON RH.[backup_set_id] = BKS.[backup_set_id]
INNER JOIN msdb..backupmediafamily BMF ON BKS.[media_set_id] = BMF.[media_set_id]
ORDER BY RH.[restore_date] DESC

Please comment if any suggestion or modification J

2013 in review

Posted: December 31, 2013 in Database Administrator

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 12,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 4 sold-out performances for that many people to see it.

Click here to see the complete report.

A user who is using SQL Server express edition, came to me and ask how to take All Database Backup at a time. Here is a simple script to take all database backup at a specified location.

DECLARE @name VARCHAR(50– database name
DECLARE 
@path VARCHAR(256––– path for backup files
DECLARE 
@fileName VARCHAR(256–– filename for backup
DECLARE 
@fileDate VARCHAR(20–– used for file name
SET 
@path ‘D:\DBBACKUP\’
SELECT @fileDate CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE 
db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE 
@@FETCH_STATUS = 0
BEGIN
SET 
@fileName @path @name ‘_’ @fileDate ‘.BAK’
BACKUP DATABASE @name  TO DISK @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE 
db_cursor
DEALLOCATE db_cursor