Posts from the ‘SQL Server’ Category

Jun 1

Finding SQL Server log file location

Here are couples of ways to find the location of SQL Server log file:-

1) Connect to the SQL Server instance from SSMS and then run below query in query analyzer

SELECT SERVERPROPERTY('ErrorLogFileName')
--------------------------------------------------------------
E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
(1 row(s) affected)

2) Connect to SQL Server instance from SSMS and navigate to Management -> SQL Server Logs and open the current log file. Then scroll down to the beginning of the log and somewhere around 7th line from bottom which will read like “Logging SQL Server messages in file ‘E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.”

3) Open SQL Server configuration manager then navigate to SQL Server service and open the properties. You will find the log location against Startup parameter field under advanced tab.

4) Go to Windows event viewer then application log and look for event id number 17111 and you can find the log location under General details.

May 1

SQL Server Agent job history getting lost or not populating

You may come across a scenario when all of a sudden you are not able to see the job history for almost all the jobs except few. Here are few things that will help you in analyzing the issue. Recently we have faced this issue in our environment below id how I manage to find the cause of this issue:-

• Connect to SSMS and open the job history by going to SQL Server Agent → Job → right click and view history, for all the jobs and check how many rows are returned. Like in this case it returned only 1188 rows.

• Now Go to SQL Server Agent → Right Click → Properties → history tab. Here check what is the maximum job history log size and maximum job history per job. In this example it is set to 15000 each. Default for maximum job history log size is 1000 and maximum job history rows per job is 100.

• Now connect to msdb database and run below query to find the row count in jobhistory table.

select COUNT(*) from sysjobhistory

-----------
15000

(1 row(s) affected)

• We have found that the history table is full. So now we need to check why it’s full and which job is writing huge data in history table. In order to find that you may have to run below query in msdb database

select b.name, a.job_id, a.run_date, COUNT(*) NumberofRun from 
sysjobhistory a, sysjobs b 
where a.job_id = b.job_id
group by a.job_id, b.name, run_date

name   job_id                               run_date    NumberofRun
----   ------					  --------    -----------
Job1   R14UIUJI-C164-5FUI-BDAD-54C3UIUJIA2B 20130328    84
Job2   269UIUJI-5902-4E0C-5FUI-57AABAUIUJI2 20130328    12217
Job3   8UIU5FUI-6B0B-446C-9B64-6805FUIIUJIB 20130328    125
Job4   UIUJIB0F-EB63-5FUI-B03D-98B235FUIUJI 20130328    2480
Job5   43CUIUJI-5FUI-4CE2-B068-AEUIUJI19E3A 20130328    84
Job6   AUIUJI5A-8166-42ED-5FUI-FFUIUJI02CD8 20130328    10

(6 row(s) affected)

• The output of above query will help you find the jobs which have more rows in jobhistory table. Like in above example you will see that Job2 is adding almost 12K rows in history table in a day. So now we need to go to this job and see what it is doing.
You can alter above query to find the actual step which is sending huge data to history table. This will help you pin pointing the step which needs attention

select b.name, a.job_id, a.step_id, a.run_date, COUNT(*) NumberofRun from 
sysjobhistory a, sysjobs b 
where a.job_id = b.job_id
group by a.job_id, b.name, a.step_id, run_date

• Now we know that the job history is getting lost because Job2 is just sending huge data to history table. In order to fix this we have to either look into the job step and modify the job so that it will not send huge number of rows to history table or consider changing the maximum job history log size and maximum job history rows per job to higher values, based on above analysis and frequency of troublesome job runs.

Apr 12

Auditing database restore in SQL Server instance

How to confirm if someone have restored a database? Which database backup file is restored? WHo did the restore? When was the database last restored?

To find the answer of all these questions you may have to run couple of queries shown below.
Please make sure you run these queries under MSDB database.

1) Below query will give us a details of which all databases has ben restored, when these were restored, who restored it and the backup set which was restored.

select restore_date,destination_database_name,user_name,
backup_set_id, restore_type, recovery
 from restorehistory

OUTPUT:

restore_date		destination_database_name	user_name	backup_set_id	restore_type	recovery

2012-08-07 13:35:29.013	TestDB_RC1		TestID_8181	358			D	1	1
2012-08-07 16:39:49.370	TestDB_RC1		TestID_5357	358			D	1	0
2012-08-07 16:41:19.680	TestDB_RC1		TestID_5357	372			I	0	1

D – Full database backup applied
I – DIfferential backup applied

2) Now to find when was these backups taken, just run the below query. Hre you may have to copy the desired backup set number from the first query.
NOTE: If no result is displayed then possibly the backup which was restored is not from this instance. That mean a backup was taken in some other environment and then restored in current.

 select backup_set_id,name, backup_start_date,backup_finish_date, 
 type, database_name from backupset
 where backup_set_id = 358 or backup_set_id = 372

OUTPUT:

backup_set_id	name						backup_start_date	backup_finish_date	type	database_name

358		TestDB_backup_2012_07_07_133002_2720281	2012-07-07 13:30:08.000	2012-07-07 13:30:22.000	D	TestDB
372		TestDB_backup_2012_07_08_230001_5563963	2012-07-08 23:00:17.000	2012-07-08 23:00:19.000	I	TestDB

3) Additionally you can look into the SQL Server error log to find when was database restored and the location where database backup file was placed for this restore.

2012-08-07 16:39:59.44 spid64      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:39:59.90 Backup      Database was restored: Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11037:12735:86, last LSN: 11037:12771:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_07_133002_2720281.bak'}). Informational message. No user action required.
2012-08-07 16:41:22.15 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:22.15 spid51      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:41:22.68 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:23.16 spid51      CHECKDB for database 'TestDB_RC1' finished without errors on 2012-07-07 11:00:16.783 (local time). This is an informational message only; no user action is required.
2012-08-07 16:41:23.16 Backup      Restore is complete on database 'TestDB_RC1'.  The database is now available.
2012-08-07 16:41:23.18 Backup      Database changes were restored. Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11040:10549:56, last LSN: 11040:10573:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_08_230001_5563963.diff'}). This is an informational message. No user action is required.

Nov 4

How to view SQL Server Performance (Perf Mon) counters collected through scheduled data collector sets?

To view the report of the counters collected through data collector sets in previous post, please follow the below steps:

Go to Start –> Run –> type “Perfmon” –> Enter. Below Screen will open.
Navigate to Reports à User Defined à TEST_1 (name of your data collector set) à Click on report under that (one report is created for each run)
In some cases you may not see the counters on the right side and it will be just empty like below.
In this case you have to click on Plus (Green on mid top) and new window will open where it will show you all the counters which were collected by this data collector set ; depending on the number of counters selected.
Select the counters for which you want to view the report and click OK.
Now you can see the reports for the specific counters only.

Oct 28

How to Schedule a job to collect SQL Server Performance Monitor (PerfMon) counters?

Please follow below steps if you would like to schedule a job to collect SQL Server (or any other OS related) performance counter over the spam of hours/days/etc. Here we will create a manual data collector set and will schedule it to run as per our requirement.

NOTE:- These steps will work on Windows Server 2008 or higher edition only

Go to Start –> Run –> type “Perfmon” –> Enter. Below Screen will open.
Expand Data Collector Sets à right click on “User Defined” –> New à Data Collector Set
Give name to new data collector set and select “Create Manually”. Click Next.
Select Create data logs and Performance counter. Then click Next.
On this screen, select how frequently you want to collect the data. Then Click on Add button to add counters which need to be collected.
This will open below window where you can select the counters to be collected.

In this example we will collect below events/counters to check SQL Server performance:

Selected Buffer cache hit ratio, Free pages, Lazy writes/sec and Page life expectancy events under SQLServer:Buffer Manager counter.

Once the counters are selected Click OK.
Here you can view the counters that will be collected. Add/Remove if required. Then click next when ready to go to next step.
Select directory where you want to save the log and click next.
You can set this data collector set to run under different account if required. Here we will just leave it default and just save this new data collector set. Click Finish.
Now you will be able to see this new data collector set under “Data Collector Sets”.
- To start/stop Test_1, just right click on Test_1 and select Start/Stop. You can also schedule this by going to the properties of TEST_1 data collector set.